Problem with SQL


#1

Does anybody know why this is not working:

“SELECT DISTINCT(rowerid), age, weight, city, state, country, date_rowed, distance, time, comments FROM workouts WHERE distance=‘500’ ORDER BY time ASC”

It keeps selecting all the values, even if they are the same rowerid


#2

[quote]Does anybody know why this is not working:

“SELECT DISTINCT(rowerid), age, weight, city, state, country, date_rowed, distance, time, comments FROM workouts WHERE distance=‘500’ ORDER BY time ASC”

It keeps selecting all the values, even if they are the same rowerid[/quote]
DISTINCT is an option, not a function like COUNT().

“The ALL, DISTINCT, and DISTINCTROW options specify whether duplicate rows should be returned. If none of these options are given, the default is ALL (all matching rows are returned). DISTINCT and DISTINCTROW are synonyms and specify removal of duplicate rows from the result set.”

See http://www.w3schools.com/SQl/sql_distinct.asp for an example of its use.

:cool: openvein.org -//-


#3

Ok than how would you go about limiting than?. I want to select all the entries, but keep only the fastest time for each user. (like a leaderboard). Unless I am mistaken, DISTINCT wouldnt help me in this case. I tried GROUP BY but that gave me the slowest time… which I need the fastest.


#4

SELECT rowerid, MIN(time) FROM workouts GROUP BY rowerid

This query will give you the rowerid and lowest time for that rowerid.

So we can use that as a subquery to find the row that matches those two values.

SELECT rowerid, date_rowed, time FROM workouts WHERE (rowerid, time) IN (“lowest times for each rowerid”) GROUP BY rowerid ORDER BY time

This query would give us the leaderboard given that we can get “lowest times for each rowerid” which is done with the first query. So just replace:

SELECT rowerid, date_rowed, time FROM workouts WHERE (rowerid, time) IN (SELECT rowerid, MIN(time) FROM workouts GROUP BY rowerid) GROUP BY rowerid ORDER BY time

:cool: openvein.org -//-