arik arik - 1 year ago 59
MySQL Question

SQL order stop at ID

I have the MySQL table


I am trying to order the table by points (several users can have the same amount of points), however: I am trying to only have the entries before a certain ID. So, if the query first makes an ordered table, I would like to have all its entries, sorted, but stop at ID = '40', because I'm only interested at the players who are ordered higher (not necessarily better, they can have the same amount of points) than the player #40.

If it is, then how is it possible?

EDIT: No, I do NOT want to limit the query results to 40 rows, for I do not know how many players are better than player #40. I only want the query to stop at the entry with ID=40, NOT with row_number=40.

EDIT 2: Problem is: the players with more points than player #40 can still have higher IDs, which is why it's not possible to just say
WHERE ID <= 40


enter image description here

Seeing this picture, I simply want the results from player
until player
, ALTHOUGH player
has the same amount of points as
has. Is that possible somehow? The names are arbitrary, by the way, I just didn't think of any better usernames.

Answer Source

If I understand you question correct, you are looking for a list of players who have more points than the point value of player at id 40.

If so:

SELECT * FROM table WHERE points > (SELECT b.points FROM table b WHERE b.ID = '40') ORDER BY points DESC

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download