SQL - Correct query for sorting items by values from other table

Question

Hi there!

I've got a little problem with correct query and hope you can help me. My testing tables structures are as follow:

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`),
) ;

CREATE TABLE IF NOT EXISTS `user_throws` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `length` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`,`user_id`),
) ;

The relations between them is many to one (a user can have many throws). The thing is I need to make a somekind ranking page for all users let's say top 100 throwers. So I need a query result to be:

1. Filtered by users so only the users who have any throws and the length of throw is greater than 0 is correct record.
2. Filtered by duplicates so only unique users appear on the list (GROUP BY I suppose).
3. Sorted by length of throws so only the best (greatest) result will be appended.

For that purpose I wrote the query:

SELECT  `user_throws`.`length` , `users` . * 
FROM  `users` 
JOIN  `user_throws` ON (  `user_throws`.`user_id` =  `users`.`id` ) 
WHERE  `user_throws`.`length` >  '0'
GROUP BY  `users`.`id` 
ORDER BY  `user_throws`.`length` DESC 

But the problem is results are not being sorted by longest throw. It filters the throws with length greater than 0 and displays unique users but the length appended does not correspond with desired sorting. I found out that the length value is the value of throw with lowest user_throws.id that is associated with the user. How should the correct query look like?

Answer 1

Try:

SELECT longthrow,id,name FROM (
SELECT max(t.length) longthrow,u.id,u.name
FROM users u, user_throws t
WHERE u.id=t.user_id
 AND t.length>0
GROUP BY u.id
) ORDER BY longthrow DESC

Which should give you the longest throw of each user, sorted by furthest throw first, shortest last - think that's what you wanted ;)

And I should add before the comments show up, it's better to start it with:

SELECT max(t.length) longthrow,u.id,u.name

Than:

SELECT max(t.length) longthrow,u.*

Answer 2

How about

SELECT MAX(`user_throws`.`length`) , `users` . * 
...

Answer 3

add a having clause after the group by

SELECT  `user_throws`.`length` , `users` . * 
FROM  `users` 
JOIN  `user_throws` ON (  `user_throws`.`user_id` =  `users`.`id` ) 
GROUP BY  `users`.`id` 
HAVING `user_throws`.`length` > 0
ORDER BY  `user_throws`.`length` DESC 

EDIT: I see now that this isn't what you're looking for. You're looking for the best throw from each user. Some other answers are more applicable.

Answer 4

You could try a subquery solution that looks something like this:

   SELECT  
      (SELECT MAX(length) FROM user_throws WHERE user_id=users.id) AS LongThrow,
      users.* 
   FROM  users
   WHERE (SELECT MAX(length) FROM user_throws WHERE user_id=users.id) >  '0'
   ORDER BY (SELECT MAX(length) FROM user_throws WHERE user_id=users.id) 

Note: The syntax is off the top of my head... Please take with a grain of salt. Also, the query above is highly verbose but should work out of the gate. You can abbreviate it as you see fit.

But in any case, the subquery idea will work. You might just have to tweak/play around with the syntax.

Answer 5

try

SELECT  u2.`length` , u . * 
FROM  `users` u ,  `user_throws` u2 where u2.`user_id` =  u.`id` 
AND  u2.`length` >  '0' and u2.`id` = (select u1.id from `user_throws` u1 where u1.`user_id` =  u.id  order by u1.`length` desc limit 1)
GROUP BY  u2.`user_id` 

Answer 6

SELECT user_throws2.length , users . * FROM users JOIN (SELECT * FROM user_throws ORDER BY length DESC ) as user_throws2 ON ( user_throws2.user_id = users.id ) WHERE user_throws2.length > '0' GROUP BY users.id ORDER BY user_throws2.length DESC

try it, works on last mysql (if not work in you mysql update that :D)

Answer 7

A slightly different approach:

select ut.longest, u.id, u.name
from users u
join
(select user_id, max(length) longest
 from user_throws
 where length > 0
 group by user_id) ut on u.id = ut.user_id
order by ut.longest desc

Answer 8

SELECT MAX(user_throws.length) , users . * FROM users JOIN user_throws ON ( user_throws.user_id = users.id ) WHERE user_throws.length > '0' GROUP BY users.id ORDER BY user_throws.length DESC

i try that, and it works, just check execution time for the two options