Select a row with least value of a column using where and group by

Question

Sample table:

id------user_id------grade_id------time_stamp

1---------100----------1001---------2013-08-29 15:07:38

2---------101----------1002---------2013-08-29 16:07:38

3---------100----------1001---------2013-08-29 17:07:38

4---------102----------1003---------2013-08-29 18:07:38

5---------103----------1004---------2013-08-29 19:07:38

6---------105----------1002---------2013-08-29 20:07:38

6---------100----------1002---------2013-08-29 21:07:38

I want to select rows whose user_id = 100 group by grade_id only if its time_stamp is least for that particular grade_id.

so, from the above table, it should be:

row 1 because its time_stamp is least for that value of grade_id(1001)

but not row 2 because I only want 1 row for a particular grade_id

also not row 6 because that particular grade_id has least value for user_id 105.

I tried few things, which are too basic and obviously not worth posting.

Thank You

What database are you using? Some databases have built-in functions for this, others require you to write your own SQL for it. — Barmar, Sep 02 '13 at 11:17
possible duplicate of [Fetch the row which has the Max value for a column](http://stackoverflow.com/questions/121387/fetch-the-row-which-has-the-max-value-for-a-column) — Barmar, Sep 02 '13 at 11:22

score 1 · Accepted Answer · answered Sep 02 '13 at 11:12

You could try nested queries:

SELECT grade_id, COUNT(grade_id) FROM SAMPLE_TABLE ST WHERE time_stamp = (SELECT MIN(time_stamp) FROM SAMPLE_TABLE STT WHERE STT.grade_id = ST.grade_id) AND user_id = 100 GROUP BY grade_id;

In this case, the nested query will give you the minimun timestamp for each specific 'grade_id' and you can use it in your WHERE filter.

score 1 · Answer 2 · answered Sep 02 '13 at 11:23

1

SELECT t.*
FROM tableX AS t
  JOIN
    ( SELECT grade_id, MIN(time_stamp) AS time_stamp
      FROM tableX
      GROUP BY grade_id
    ) AS g
      ON  g.grade_id = t.grade_id
      AND g.time_stamp = t.time_stamp
WHERE t.user_id = 100 ;

answered Sep 02 '13 at 11:23

ypercubeᵀᴹ

113,259
19
174
235

Select a row with least value of a column using where and group by

2 Answers2