Xaprb

How to simulate the SQL ROW_NUMBER function

with 11 comments

create table fruits (
    type varchar(10) not null,
    variety varchar(20) not null,
    primary key(type, variety));

insert into fruits values
('apple', 'gala'),
('apple', 'fuji'),
('apple', 'limbertwig'),
('orange', 'valencia'),
('orange', 'navel'),
('pear', 'bradford'),
('pear', 'bartlett'),
('cherry', 'bing'),
('cherry', 'chelan');

select l.type, l.variety, count(*) as num
from fruits as l
left outer join fruits as r
    on l.type = r.type
    and l.variety >= r.variety
group by l.type, l.variety;

+--------+------------+-----+
| type   | variety    | num |
+--------+------------+-----+
| apple  | fuji       |   1 |
| apple  | gala       |   2 |
| apple  | limbertwig |   3 |
| cherry | bing       |   1 |
| cherry | chelan     |   2 |
| orange | navel      |   1 |
| orange | valencia   |   2 |
| pear   | bartlett   |   1 |
| pear   | bradford   |   2 |
+--------+------------+-----+

11 Responses to 'How to simulate the SQL ROW_NUMBER function'

Subscribe to comments with RSS

1. I appreciate the above article with explanatory example and the below line

   “This effectively makes the join a CROSS JOIN, which is inefficient (O(n2)). “

   Good…

   

   Shankudada

   1 Jan 07 at 6:12 am
2. Here is a follow-up showing how to number rows using user variables.

   

   Stephen Gornick

   26 Mar 07 at 9:41 pm
3. FYI, the Row_Number function as many other analytic function,
   Exists in oracle way back from version 8i, which was something like 10 years ago from now….

   

   Oracle Guy

   21 Aug 08 at 4:15 pm
4. Hi Baron,

   I just re-read your ROW_NUMBER articles, and I am happy to contribute another method using GROUP_CONCAT():

   select l.type, l.variety,
   find_in_set(l.variety, (
   select group_concat(i.variety order by i.variety)
   from fruits i
   where i.type = l.type
   )) num
   from fruits l;

   As always, be cautious for warnings like:

   x line(s) were cut by GROUP_CONCAT()

   You can try to get rid of them using

   SET group_concat_max_len := @@max_allowed_packet

   

   Roland Bouman

   5 Sep 08 at 6:59 am
5. I tried to apply this working with two tables: categories (category_id, parentid) and products (category_id, name). I would like to group products by parent category (parentid) and limit the mumber of products for each group for exemple to 3.

   I can’t combine the code above and the join clause (except writing the same request twice creating two temporaries tables).

   How should I do in a more smart way ?

   

   sylvie

   10 Aug 09 at 6:24 pm
6. This does not seem to simulate the ROW_NUMBER() function, because if you were to apply this on numerical fields, same values would get the same number. this is a simulation of the RANK() function.

   

   Blecchi

   9 Jan 10 at 6:52 am
7. One more solution,

   SET @n := 0;
   SET @t := NULL;
   SELECT f.*, @n
   FROM fruits f
   WHERE CASE WHEN f.type @t THEN @n := 0 ELSE 1 END >= 0
   AND(@n := @n + 1) >= 0
   AND(@t := f.type) IS NOT NULL

   

   Shiva M

   15 Feb 10 at 7:21 pm
8. In my above comment “not equal to” with greater than and less than got gobbled up. Hence replaced it with “!=”.

   SET @n := 0;
   SET @t := NULL;
   SELECT f.*, @n
   FROM fruits f
   WHERE CASE WHEN f.type != @t THEN @n := 0 ELSE 1 END >= 0
   AND(@n := @n + 1) >= 0
   AND(@t := f.type) IS NOT NULL

   

   Shiva M

   15 Feb 10 at 7:28 pm
9. [...] Eine weitere Möglichkeit beschreibt Xarpb in seinem Artikel How to simulate the SQL ROW_NUMBER function. [...]

   

   Zeilen in MySQL enumerieren Lars seine Seite

   15 Mar 10 at 7:14 pm
10. If you want a simple way to reproduce the row_number without grouping its easily achieved with a user variable.

    SELECT *, @rownum:=@rownum+1 rownum, (SELECT @rownum:=0) dummy
    FROM fruits
    HAVING rownum <= (pg*lim);

    You'll notice I used it for dynamic paging of the result set in my stored proc.

    

    Brian M

    14 Sep 10 at 11:43 am
11. Hi

    Thank you for useful articles.
    But there is problem!

    in below sample (in SQL SERVER):

    CREATE TABLE [Test] (
    [ID] [bigint] IDENTITY (1, 1) NOT NULL ,
    [Date] [char] (10),
    [B] [int],
    [C] [int]
    )
    INSERT INTO [TEST].[Test]
    VALUES (’1389/01/01′,’1′,’1′)
    INSERT INTO [TEST].[Test]
    VALUES (’1389/01/01′,’5′,’2′)
    INSERT INT[TEST].[Test]
    VALUES (’1389/01/02′,’2′,’10′)
    INSERT INT[TEST].[Test]
    VALUES (’1389/01/02′,’2′,’11′)
    INSERT INT[TEST].[Test]
    VALUES (’1389/01/03′,’2′,’2′)
    INSERT INT[TEST].[Test]
    VALUES (’1389/01/03′,’5′,’1′)
    INSERT INT[TEST].[Test]
    VALUES (’1389/01/01′,’2′,’1′)
    INSERT INT[TEST].[Test]
    VALUES (’1389/01/02′,’2′,’12′)
    INSERT INT[TEST].[Test]
    VALUES (’1389/01/03′,’2′,’2′)

    when we use:

    select l.Date, l.B, l.C, count(*) as num
    from Test as l
    left outer join Test as r
    on l.Date = r.Date
    and l.B >= r.B
    group by l.Date, l.B, l.C

    we have:

    …
    1389/01/02 2 10 3
    1389/01/02 2 11 3
    1389/01/02 2 12 3
    …

    But must be create like :

    …
    1389/01/02 2 10 1
    1389/01/02 2 11 2
    1389/01/02 2 12 3
    …

    No my question:
    What solve it? (Create above table)?
    Note : I want use SQL only (No use TSQL)!
    Note 2 : Cause problem is ‘Like Data in Date and B Field’

    Description 1: My english is poor.Excuse me!
    Description 2: Date is persian calender!

    

    SMAH1

    21 Feb 11 at 2:24 pm

Leave a Reply