KISKE KISKE - 18 days ago 6
MySQL Question

GROUP BY column and get the best from others column

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.

Answer

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
Comments