CODEWITHSUNDEEP
mysqlsqlgroup-by
Barry Connolly
Barry Connolly

Reputation: 663

mysql unique statement

I have a table like so 

ID     |   date  |    CODE 

1        01-02-13     VDOT
2        03-02-13     CMAX
3        05-02-13     VDOT
1        05-02-13     CMAX
1        09-02-13     VDOT

My SQL query is as follows 

SELECT * FROM Table_Name ;

Which is obviously showing all the results but I need to only show one instance of each code where the date is most recent so my result needs to look like this

1        09-02-13     VDOT
1        05-02-13     CMAX

Can anyone tell me how how to only get one result for each code (the most recent entry by date)?

Upvotes: 0

Views: 72

Answers (3)

Raheel Hasan
Raheel Hasan

Reputation: 6023

try this:

SELECT * FROM Table_Name 
GROUP BY CODE
ORDER BY date DESC

This will ORDER after it groups. hence gives out ORDERS within the grouped-results.

.

But now try this:

SELECT * FROM 
(SELECT * FROM Table_Name ORDER BY date DESC) tx2 
GROUP BY CODE

This will ensure correct date ORDER before GROUP.

Upvotes: 1

Santhosh
Santhosh

Reputation: 1791

SELECT * FROM
TABLE AS A
JOIN
(
SELECT D.CODE, MAX(D.DATE) AS DATE FROM TABLE AS D
GROUP BY D.CODE
) AS E
WHERE 
A.CODE =  E.CODE
AND A.DATE = E.DATE

Upvotes: 0

John Woo
John Woo

Reputation: 263723

SELECT  a.*
FROM    TableName a
        INNER JOIN
        (
            SELECT  CODE, MAX(date) max_date
            FROM    TableName
            GROUP   BY CODE
        ) b ON  a.Code = b.Code AND
                a.date = b.max_date

Upvotes: 2

Related Questions