MySQL - Latest Row using GROUP BY

Question

I have a table (table1):

ID   INSERTDATE    ISSUE    SOURCE
1    2011-12-01    A        EMAIL
2    2011-12-02    B        POST
3    2011-12-03    C        MEETING
4    2011-12-04    B        INTERNET

I would like to get the latest SOURCE for each issue...

SELECT * FROM table1 GROUP BY SOURCE ORDER BY ISSUE ASC

Results in

ID   INSERTDATE    ISSUE    SOURCE
1    2011-12-01    A        EMAIL
2    2011-12-02    B        POST
3    2011-12-03    C        MEETING

But how can I get the following, so that issue B has the latest source?

ID   INSERTDATE    ISSUE    SOURCE
1    2011-12-01    A        EMAIL
4    2011-12-04    B        INTERNET
3    2011-12-03    C        MEETING

score 1 · Accepted Answer · edited May 23 '17 at 11:55

1

You need an aggregate and a self join.
This is standard SQL without relying on the MySQL group by extensions (which are rubbish)

SELECT
    t.*
FROM
    ( -- get most recent ID per issue
    SELECT MAX(ID) AS MaxID, ISSUE
    FROM table1
    GROUP BY ISSUE
    ) t2
    JOIN  -- join back to get the whole row
    table1 t ON t2.MaxID = t.ID

Edit, after comment. Assumes one change per day only

SELECT
    t.*
FROM
    ( -- get most recent INSERTDATE per issue
    SELECT MAX(INSERTDATE) AS MaxINSERTDATE, ISSUE
    FROM table1
    GROUP BY ISSUE
    ) t2
    JOIN  -- join back to get the whole row
    table1 t ON t2.MaxINSERTDATE = t.ID

edited May 23 '17 at 11:55

Community

1
1

answered Dec 07 '11 at 11:01

gbn

422,506
82
585
676

I cant ensure that the highest ID is the latest issue... it has to be determined by the insertdate – craig1231 Dec 07 '11 at 11:19
If you have 2 on the same day? – gbn Dec 07 '11 at 11:21
In my real world problem, it does have a time aswell. I cant detail my real world problem for security reasons ;) – craig1231 Dec 07 '11 at 11:26

score -1 · Answer 2 · edited Dec 07 '11 at 10:51

-1

I think you should order by INSERTDATE (maybe ,ISSUE)

SELECT * FROM table1 GROUP BY SOURCE ORDER BY INSERTDATE DESC

edited Dec 07 '11 at 10:51

Sai Kalyan Kumar Akshinthala

11,704
8
43
67

answered Dec 07 '11 at 10:49

hungneox

9,333
12
49
66

MySQL - Latest Row using GROUP BY

2 Answers2