CODEWITHSUNDEEP
mysql
shantanuo
shantanuo

Reputation: 32304

top 2 values from group by

When I will group by details column and look for the date "2009-08-05" I want the earlier one day id as well.

select id,  details, abc_date from test order by details limit 10;
+------------+------------------+------------+
| id         | details          | abc_date   |
+------------+------------------+------------+
|       2224 | 10025            | 2009-08-11 | 
|       4575 | 10025            | 2009-09-02 | 
|       1617 | 10025            | 2009-08-05 | 
|       3614 | 10025            | 2009-08-24 | 
|       1811 | 10025            | 2009-08-07 | 
|        969 | 10025            | 2009-07-29 | 
|       1441 | 10025            | 2009-08-03 | 
|       4345 | 10025            | 2009-08-31 | 
|       3330 | 10025            | 2009-08-21 | 
|        799 | 10025            | 2009-07-27 | 
+------------+------------------+------------+

Upvotes: 1

Views: 223

Answers (2)

Quassnoi
Quassnoi

Reputation: 425753

SELECT  details,
        (
        SELECT  id
        FROM    test ti
        WHERE   ti.details = to.details
        ORDER BY
                date
        LIMIT 1
        ) AS first_id
FROM    test to
GROUP BY
        details

Upvotes: 1

ennuikiller
ennuikiller

Reputation: 46985

if you mean ordering by date within details (and its not clear from your question that that is what you want), try:

select id,  details, abc_date from test order by details,abc_date limit 10;

Upvotes: 0

Related Questions