user1315599 user1315599 - 1 month ago 10
MySQL Question

MySQL get item number on select

I am not an expert in MySQL and not sure how to solve this using MySQL only.

I have table like this:

mysql> describe items;
+-------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------------+------+-----+---------+----------------+
| itemID | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| date | timestamp | NO | | | | |
+-------------+---------------------+------+-----+---------+----------------+


And I would like to get the position of an item as if I will make a selection and sort (or filter). For example, I sort by date and item with itemID=79 will be item with index 126 if I will make a loop. So I would like to get this number 126 directly from database. This is probably similar to ranking but I am not sure but I am not sure if it is possible.

Answer
 SELECT itemID, date
 FROM (
           SELECT itemID, date, @rn:= @rn +1 as rank 
           FROM items, (SELECT @rn := 0) as para
           ORDER BY date
      ) T
 WHERE rank = 126

OR

 SELECT itemID, date, rank
 FROM (
           SELECT itemID, date, @rn:= @rn +1 as rank 
           FROM items, (SELECT @rn := 0) as para
           ORDER BY date
      ) T
 WHERE itemID = 79