filter by row number id of specific item id

Question

From MySQL - Get row number on select I know how to get the row number / rank using this mysql query:

SELECT @rn:=@rn+1 AS rank, itemID
FROM (
  SELECT itemID
  FROM orders
  ORDER BY somecriteria DESC
) t1, (SELECT @rn:=0) t2;

The result returns something like this:

+--------+------+
| rank | itemID |
+--------+------+
|  1   |   265  |
|  2   |   135  |
|  3   |   36   |
|  4   |   145  |
|  5   |   123  |
|  6   |   342  |
|  7   |   111  |
+--------+------+

My question is: How can I get the result in 1 simple SINGLE QUERY that returns items having lower rank than itemID of 145, i.e.:

+--------+------+
| rank | itemID |
+--------+------+ 
|  5   |   123  |
|  6   |   345  |
|  7   |   111  |
+--------+------+

Oracle sql query is also welcomed. Thanks.

DCookie · Accepted Answer · 2012-08-25T01:57:31.107

An Oracle solution (not sure if it meets your criteria of "one simple single query"):

WITH t AS
(SELECT item_id, row_number() OVER (ORDER BY some_criteria DESC) rn
   FROM orders)
SELECT t2.rn, t2.item_id
  FROM t t1 JOIN t t2 ON (t2.rn > t1.rn)
 WHERE t1.item_id = 145;

My assumption is no repeating values of item_id.

Attempting to put this in MySQL terms, perhaps something like this might work:

SELECT t2.rank, t2.itemID
  FROM (SELECT @rn:=@rn+1 AS rank, itemID
          FROM (SELECT itemID
                  FROM orders
                 ORDER BY somecriteria DESC), (SELECT @rn:=0)) t1 INNER JOIN
       (SELECT @rn:=@rn+1 AS rank, itemID
          FROM (SELECT itemID
                  FROM orders
                 ORDER BY somecriteria DESC), (SELECT @rn:=0)) t2 ON t2.rank > t1.rank
 WHERE t1.itemID = 145;

Disclaimer: I don't work with MySQL much, and it's untested. The Oracle piece works.

Thanks ! I will test them within few days before approving the right answer. — Capitaine, Aug 27 '12 at 13:26

score 1 · Answer 2 · answered Aug 24 '12 at 15:06

1

SELECT @rn:=@rn+1 AS rank, itemID
  FROM (
    SELECT itemID
    FROM orders
    ORDER BY somecriteria DESC
  ) t1, (SELECT @rn:=0) t2
where rank >
(
select rank from
   (
   SELECT @rn:=@rn+1 AS rank, itemID
   FROM 
       (
       SELECT itemID
       FROM orders
       ORDER BY somecriteria DESC
       ) t1, (SELECT @rn:=0) t2
   ) x where itemID = 145
) y

answered Aug 24 '12 at 15:06

davek

22,499
9
75
95

returns items having lower rank than itemID of 145, but not items having lower ID than itemID of 145 – Capitaine Aug 24 '12 at 15:07
I saw a part of codes is needed to be duplicated, so this is the simplest way to do so with just 1 query? – Capitaine Aug 24 '12 at 15:44
1

I can't say if it's the simplest, as I'm not 100% sure what windowing functions are available in MySql. – davek Aug 24 '12 at 15:46
just tried the one you edited, as you use "where itemID = 145" clause, the rank would always return 1 as there is only 1 row returning..., so it is not working – Capitaine Aug 24 '12 at 16:11

filter by row number id of specific item id

2 Answers2