GROUP BY column and get the best from others column

Question

I will show you with an example what I need.

This is my table with some data:

td_time | td_wave | td_userid_1 | td_userid_2
15          | 1             | 100                | 200
21          | 2             | 300                | 400
9            | 1             | 500                | 600
35          | 3             | 700                | 800
20          | 3             | 900                | 1000
88          | 4             | 1100              | 1200

I need the best td_time_seconds from every td_wave and the best users associated with that 'record'.

This is the query I used:

SELECT MIN(td_time_seconds), td_wave, td_userid_1, td_userid_2
FROM test
GROUP BY td_wave
ORDER BY td_time_seconds ASC

and return the following:

td_time | td_wave | td_userid_1 | td_userid_2
9            | 1             | 100                | 200
20          | 3             | 700                | 800
21          | 2             | 300                | 400
88          | 4             | 1100              | 1200

The problem, the query return the first td_userid_1 and td_userid_2.

td_userid_1 and td_userid_2 when td_wave = 1 should be 500 and 600 and when td_wave = 3 should be 900 and 1000.

Please add all info also directly into the question. Links break, especially SQLFiddle — juergen d, Nov 14 '16 at 20:47

score 1 · Accepted Answer · answered Nov 14 '16 at 21:07

You can use an in clause and a subquery using tuple

   select td_time , td_wave , td_userid_1 , td_userid_2
   from my_table 
   where (td_wave, td_time) in  (
      select td_wave, min(td_time)
      from my_table
      group by td_wave
   )
  ORDER BY td_time_seconds ASC

GROUP BY column and get the best from others column

1 Answers1