Select data with max date order by another field

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.

Here it shows every payment of students. for printing report I only need to retrive details of final payment details of each student. @Stefano — Vikum Dheemantha, Mar 06 '17 at 09:04

score 3 · Accepted Answer · answered Mar 06 '17 at 09:31

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)

this works for me well, thanks – Vikum Dheemantha Mar 06 '17 at 10:35 — Vikum Dheemantha, Mar 06 '17 at 10:35

score 0 · Answer 2 · answered Mar 06 '17 at 09:04

0

SELECT * FROM table group by Reg_No having max(Payday)

Payday Must be of date type else use format function to convert into date

answered Mar 06 '17 at 09:04

Rakesh Kumar

4,319
2
17
30

having max(payday). Um, you sure? – Strawberry Mar 06 '17 at 09:26
yes this should work, if not then you can go for having Payday = max(Payday) – Rakesh Kumar Mar 06 '17 at 09:29
No. This shouldn't work, and it won't. – Strawberry Mar 06 '17 at 09:32
@Strawberry one more option ``SELECT * FROM table group by Reg_No order by Payday desc;`` – Rakesh Kumar Mar 06 '17 at 09:37
I've provided one of 3 documented solutions. – Strawberry Mar 06 '17 at 09:47
@Strawberry which? – Rakesh Kumar Mar 06 '17 at 09:51
The most efficient one. – Strawberry Mar 06 '17 at 10:34

Sampath Munaweera · Answer 3 · 2017-03-06T09:23:18.263

-1

is this Payday column data format under date format or just varchar?

try this code

SELECT * FROM Table where (SELECT MAX(STR_TO_DATE(Payday, '%m-%d-%Y'))) group by Reg_No

edited Mar 06 '17 at 09:23

answered Mar 06 '17 at 09:03

Sampath Munaweera

119
2
13

payday column is under date format. – Vikum Dheemantha Mar 06 '17 at 09:06
SELECT * FROM Table where (SELECT MAX(STR_TO_DATE(Payday, '%m-%d-%Y'))) group by Reg_No – Sampath Munaweera Mar 06 '17 at 09:22

Select data with max date order by another field

3 Answers3

Linked

Related