CODEWITHSUNDEEP
mysql
Keir Simmons
Keir Simmons

Reputation: 1684

Complicated MySQL sorting for competition position

I have a MySQL table for competitions called users. Now, I want to be able to make a select statement that sorts this table by post_comp DESC, posted_last ASC. After this sort, whoever is returned first is #1, second row is #2 etc etc. However, I want to take this further and return the position of a specific user in the table. Now, each user has their own id, zbid. I also need to add WHERE cid=3 AND post_comp>0 to the end of the query.

This is what I have tried:

SELECT u.zbid,u.post_comp, @curRow := @curRow + 1 AS row, IF(u.zbid=123,1,0) AS me FROM users u, (SELECT @curRow := 0) r WHERE cid=3 AND post_comp > 0 ORDER BY u.post_comp DESC, u.posted_last ASC

Now what that does is return the following: zbid, post_comp, row, me where me is 1 if it is the row I specifically want to return, and 0 otherwise. So if I am looking for the position of the user with zbid=123, it sorts the table, brings all the data back to me, and wherever the user with zbid=123 is, will also return a me=1 result. row denotes the user's position in the competition.

Now, I this works fine, only problem is that it returns all of these rows, and I only want it to return the one row with me=1. I tried appending another ORDER BY me DESC LIMIT 1 to the very end but that messed up the previous ordering and didn't work. Any other suggestions?

Upvotes: 0

Views: 200

Answers (1)

invertedSpear
invertedSpear

Reputation: 11054

SELECT * 
FROM (
  SELECT u.zbid,u.post_comp, @curRow := @curRow + 1 AS row, 
         IF(u.zbid=123,1,0) AS me 
  FROM users u, (SELECT @curRow := 0) r 
  WHERE cid=3 AND post_comp > 0 
  ORDER BY u.post_comp DESC, u.posted_last ASC
) temp 
WHERE me=1

Upvotes: 2

Related Questions