Select data with max date order by another field [duplicate]

Question

I have created this table in SQL database;

index  Reg_No   Payment  Payday
1      S001     100      2017/01/01
2      S001     500      2017/02/01
3      S002     400      2017/01/01
4      S002     1000     2017/11/01

I need to group this by reg_no and also show the latest payment only. So, final result should look like this.

index    Reg_No    Payment    Payday
2        S001      500        2017/02/01
4        S002      1000       2017/11/01

How can I write the SQL query for this.

Answer 1

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table 
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,reg_no CHAR(4) NOT NULL
,payment INT NOT NULL
,payday DATE NOT NULL
);

INSERT INTO my_table VALUES
(1,'S001',100,'2017/01/01'),
(2,'S001',500,'2017/02/01'),
(3,'S002',400,'2017/01/01'),
(4,'S002',1000,'2017/11/01');

SELECT x.* 
  FROM my_table x 
  JOIN 
     ( SELECT reg_no
            , MAX(payday) payday 
         FROM my_table 
        GROUP 
           BY reg_no
     ) y 
    ON y.reg_no = x.reg_no 
   AND y.payday = x.payday;
+----+--------+---------+------------+
| id | reg_no | payment | payday     |
+----+--------+---------+------------+
|  2 | S001   |     500 | 2017-02-01 |
|  4 | S002   |    1000 | 2017-11-01 |
+----+--------+---------+------------+
2 rows in set (0.01 sec)