Keeto Keeto - 1 year ago 90
MySQL Question

mysql statement to get the rank of a given row id in an ordered result set

I have a mysql table with multiple columns. The primary key of the table is the 'id' column. A row has multiple columns but the most relevant one for this question is 'Date' which is basically a timestamp. What is an efficient way to get the order (rank) of a given row id if I want to order the rows by their timestamp. The most recent timestamp is of rank 1, the second is of rank 2 and so on. I want to return the rank of a given row

Edit: I use ORDER BY to get an ordered set but I want the mysql statement to return the order of the specific item, not an ordered rows. I also don't want to parse the result set since this is very time cosnuming

Edit2: for example assume the following table

id timestamp name
1 Dec 4, 2016 Bob
2 Jan 1, 2015 Eve
3 Feb 6, 2017 Alice

Given an id, I should return the order of the item

id=1, expected output: 2
id=2, expected output: 3 (least recent)
id=3, expected output: 1 (most recent)

Answer Source

answer with out edit :Use row_number () over (order by ) clause and put some *dummy

select ROW_NUMBER()over(order by (select1) ) as renk ,* from table

answer after edit in question..

select ROW_NUMBER()over(order by timestamp  desc ) as renk ,* from table 
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download