Mysql – How to get the user not to be listed twice

MySQL

I'm using the following query to list best times in a race. This works perfectly well but someone does the best record twice (for example, they finished the race in 15 seconds twice) they being listed twice. I don't want them to be listed again.

SELECT
    *
FROM
    racerecords
WHERE
    (holder_regid, rec_time) IN (
        SELECT
            holder_regid,
            MIN(rec_time)
        FROM
            racerecords
        WHERE
            race_id = 17
        GROUP BY
            holder_regid
    )
AND race_id = 17 ORDER BY rec_time;

Additional Info: holder_regid is the registration ID of the racer and the rec_time is the record time.

So how can I achive this? I tried to use DISTINCT but I got error. Help me with the query.

Best Answer

Try the SQL below. I could only create a few sample records, so maybe I haven't covered everything.

BTW, welcome to the forum. In future, when you are asking questions like this one, it would be great if you could provide DDL in the form of CREATE TABLE MyTable(col1, col1_type, col2...) and some sample DML (INSERT INTO TABLE MyTABLE VALUES(val1, val2...). You can put this up on SQLFiddle for example - or here of course if there's not too much data - you can always provide a downloadable script.

Good questions tend to get better answers - check out this (and links within) to see how to get the best out of this forum - it really is a great resource. As it says, help us to help you!

SELECT
    *
FROM
    racerecords
WHERE
    (holder_regid, rec_time) IN (
        SELECT
            holder_regid,
            MIN(rec_time)
        FROM
            racerecords
        WHERE
            race_id = 17
        GROUP BY
            holder_regid
    )
AND race_id = 17 
GROUP BY holder_regid   <<=== added this line
ORDER BY rec_time, holder_regid;