Get The max value row from a table in mysql

Question

I have 2 table in mysql callde 'Complaint' and 'Action'. I want to get the last action taken for each complaint. Tables are:

Complaint:
complaint_id    complaint_title
--------------------------------
1               Abc complanint
2               Xyz complaint
3               Dummy Complaint

Action:
ser_id    complaint_id    action_id    action_date
1         1               1            2018-09-05
2         1               2            2018-09-07
3         1               3            2018-09-10
4         2               1            2018-09-08
5         3               1            2018-09-15
6         3               2            2018-09-18

now I want to get the result like:

ser_id    complaint_id    action_id    action_date
3         1               3            2018-09-10
4         2               1            2018-09-08
6         3               2            2018-09-18

DB fiddle

Which MySQL version are you using? – jarlh Oct 02 '18 at 06:09 — jarlh, Oct 02 '18 at 06:09
The only thing missing is your best attempt so far – Strawberry Oct 02 '18 at 06:39 — Strawberry, Oct 02 '18 at 06:39

Zaynul Abadin Tuhin · Answer 1 · 2018-10-02T06:33:51.137

1

use join and group by ,For your expected result below query is need,

    select complaint_id, 
   max(ser_id),
   max(action_id),
   max(action_date) 
   from Action a join Complaint c on a.complaint_id=c.complaint_id
  group by complaint_id

Or you can use in

SELECT a.*
    FROM Complaint c
    JOIN Action a ON a.complaint_id = c.complaint_id AND
        a.action_date in (SELECT MAX(action_date) 
                         FROM Action a1 
                         WHERE a1.complaint_id = c.complaint_id)

https://www.db-fiddle.com/f/DcKnndn4ZhaiTsJJj5gBG/2

edited Oct 02 '18 at 06:33

answered Oct 02 '18 at 05:59

Zaynul Abadin Tuhin

31,407
5
33
63

actually i have more columns on the Action table. I need all the column of the action table for the maximum 'action_id' row. – Tahmidur Rahman Oct 02 '18 at 06:06
@TahmidurRahman you can you can use co-related subquery or you can use sub-query – Zaynul Abadin Tuhin Oct 02 '18 at 06:14
@TahmidurRahman https://www.db-fiddle.com/f/DcKnndn4ZhaiTsJJj5gBG/2 – Zaynul Abadin Tuhin Oct 02 '18 at 06:33

score 1 · Answer 2 · answered Oct 02 '18 at 06:19

This query will give you the results you want. It joins Complaints to Actions on complaint_id but also on the row having the MAX(action_date) for the complaint_id:

SELECT a.*
FROM Complaints c
JOIN Actions a ON a.complaint_id = c.complaint_id AND
    a.action_date = (SELECT MAX(action_date) 
                     FROM Actions a1 
                     WHERE a1.complaint_id = c.complaint_id)

Output:

ser_id  complaint_id    action_id   action_date
3       1               3           2018-09-10
4       2               1           2018-09-08
6       3               2           2018-09-18

SQLFiddle demo

score 1 · Answer 3 · answered Oct 02 '18 at 06:20

Try this:

select @rn := 1, @complaint_id_lag := 0;

select user_id, complaint_id, action_id, action_date from (
  select case when complaint_id = @complaint_id_lag then @rn := @rn + 1 else @rn := 1 end rn,
         @complaint_id_lag := complaint_id complaint_id,
         user_id, action_id, action_date
  from Action
  order by complaint_id, action_date desc
) a where rn = 1;

Demo

Get The max value row from a table in mysql

3 Answers3