Getting Corresponding Row Values to a GROUP BY with MIN

Question

I've got data that's something like this:

+------------+---------+-------+
|    Name    |  Time   | Flag  |
+------------+---------+-------+
| Bob        | 401     | 1     |
| Bob        | 204     | 0     |
| Dan        | 402     | 1     |
| Dan        | 210     | 0     |
| Jeff       | 204     | 0     |
| Fred       | 407     | 1     |
| Mike       | 415     | 1     |
| Mike       | 238     | 0     |
+------------+---------+-------+

I want to get each person's best time, but if the "flag" is set, their time should be divided by 2. For example, Bob's best time would be 200.5

Now I can do a relatively simple query to get this data like this:

SELECT userid,
       MIN(CASE WHEN flag = 1 THEN time / 2 ELSE time END) AS convertedTime,
       time,
       flag
FROM   times t
GROUP  BY userid
ORDER  BY convertedtime ASC

The problem here is that is doesn't return the proper corresponding data for the time and flag, getting this data:

Bob     200.5   204     0

instead of the correct data

Bob     200.5   401     1

Of course I see the issue with the previous query and I've fixed it with this:

 SELECT userid,
       MIN(convertedtime) AS convertedTime,
       (SELECT time
        FROM   times
        WHERE  MIN(convertedtime) = CASE
                                      WHEN flag = 1 THEN time / 2
                                      ELSE time
                                    end
        LIMIT  1)         AS time,
       (SELECT flag
        FROM   times
        WHERE  MIN(convertedtime) = CASE
                                      WHEN flag = 1 THEN time / 2
                                      ELSE time
                                    end
        LIMIT  1)         AS flag
FROM   (SELECT userid,
               CASE
                 WHEN flag = 1 THEN time / 2
                 ELSE time
               end AS convertedTime,
               time,
               flag
        FROM   times t) AS t
GROUP  BY userid
ORDER  BY convertedtime ASC

SQLFiddle

This does work, but I feel like there has to be a better and more efficient way of doing this. In my actual query, the part where I'm dividing the time by 2 is a much longer formula and I've got thousands of rows so it's very slow.

So the question is, is there a better/more efficient query for this?

score 1 · Accepted Answer · answered Nov 06 '17 at 22:03

1

Use a common technique from SQL select only rows with max value on a column but add the flag check when comparing the values in the ON condition.

SELECT t1.username, t2.convertedtime, t1.time, t1.flag
FROM times AS t1
JOIN (SELECT username,
             MIN(IF(flag = 1, time/2, time) AS convertedtime
      FROM times
      GROUP BY username) AS t2
ON t1.username = t2.username
AND t1.time = IF(t1.flag = 1, t2.convertedtime * 2, t2.convertedtime)

This probably won't be very efficient -- I don't think it will be able to use an index for the conditional comparison.

answered Nov 06 '17 at 22:03

Barmar

741,623
53
500
612

Trying out a similar join query now. Will report back with results – abney317 Nov 06 '17 at 22:10
Definitely runs a lot faster :) – abney317 Nov 06 '17 at 22:16

score 0 · Answer 2 · answered Nov 06 '17 at 22:19

This is what I ended up using (couldn't multiply by 2 to revert the number in this case due to my more complicated actual query having rounding in it), but Barmar did pretty much the same thing as I'm doing so I marked his as the answer.

SELECT times.userID, times2.convertedTime, times.time, times.flag
FROM times JOIN
(
SELECT userid,
       MIN(CASE WHEN flag = 1 THEN time / 2 ELSE time END) AS convertedTime,
       time,
       flag
FROM   times t
GROUP  BY userid
ORDER  BY convertedtime ASC
) as times2 ON times.userid = times2.userid AND 
(
  (times.time / 2 = times2.convertedTime AND times.flag = 1) OR 
  (times.time = times2.convertedTime AND times.flag = 0)
)

Getting Corresponding Row Values to a GROUP BY with MIN

2 Answers2