0

I recently created a question, although it wasn't worded well and had no examples, a member suggested I should recreate the question, as the past had no answer.

I'm trying to get a row's position from an ordered query, in the most efficient way possible. I'm planning on having to search over 10,000 results, so I need to be as efficient as possible.

I currently have 

SELECT p.*, 
    (SELECT count(DISTINCT prank)+1 
     FROM uprofile WHERE prank > p.prank
    ) AS POSITION 
    FROM uprofile p 
    WHERE uid = " . $profile->data()->uid . " 
    ORDER BY prank DESC

Which should output POSITION as 2; uid is the value 31, and uprofile's row that has a uid value of '31' has the second most player prank (prank).

This all works good, except from two rows having the same reputation. My current data, only 3 rows are populated with player rank. The rest of the dummy rows have prank set to 0, which causes their position to all be 4.

How would I modify the SQL to return unique values, rather than some rows having the same position?

Jay Blanchard
  • 34,243
  • 16
  • 77
  • 119
Kondax Design
  • 175
  • 1
  • 1
  • 15
  • As it seems the order is defined by prank desc, just use a "Row_number" of sorts... Similar responses: http://stackoverflow.com/questions/1895110/row-number-in-mysql – xQbert Sep 29 '14 at 18:50

1 Answers1

0

mySQL doesn't support Row_Number, but you can emulate it by using the following:

SET @Position=0;

SELECT p.*, @Position:=@Position+1 POSITION 
FROM uprofile p 
WHERE uid = " . $profile->data()->uid . " 
ORDER BY prank DESC;

It seems all you're after is just the numbers 1-X where X is the maximum rows in uprofile. It seems you don't care what row number a user is assigned when prank is the same for users just so long as they are sequential. The above should handle that...

Working example of variable usage in mysql found in this Stack response

Community
  • 1
  • 1
xQbert
  • 34,733
  • 2
  • 41
  • 62
  • Hey there, thanks for the response. I've tried that, although it's returning no rows at all. I'm not sure what's incorrect about it, unfortunately. – Kondax Design Sep 29 '14 at 19:05
  • Break it down. Eliminate the where clause first and see if you get results. If you still don't review the link at the end "Stack Response" this works, I may have typo'd something. – xQbert Sep 29 '14 at 19:08
  • Removing the where clause also returns nothing, same with using the linked response. – Kondax Design Sep 29 '14 at 20:59