CODEWITHSUNDEEP
mysqlsql
user961882
user961882

Reputation: 237

MySQL position in recordset

I'm ordering a recordset like this:

SELECT * FROM leaderboards ORDER BY time ASC, percent DESC

Say I have the id of the record which relates to you, how can I find out what position it is in the recordset, as ordered above?

I understand if it was just ordered by say 'time' I could

SELECT count from table where time < your_id

But having 2 ORDER BYs has confused me.

Upvotes: 0

Views: 148

Answers (2)

Ed Gibbs
Ed Gibbs

Reputation: 26353

You can use a variable to assign a counter:

SELECT *, @ctr := @ctr + 1 AS RowNumber
FROM leaderboards, (SELECT @ctr := 0) c
ORDER BY time ASC, percent DESC

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270431

Does this do what you want?

SELECT count(*)
FROM leaderboards lb cross join
     (select * from leaderboards where id = MYID) theone
WHERE lb.time < theone.time or
      (lb.time = theone.time and lb.percent >= theone.percent);

This assumes that there are no duplicates for time, percent.

Upvotes: 0

Related Questions