row_nums without using row_number function?

Question

SELECT 
    *, (SELECT count(1) from color i WHERE i.NAME <= c.NAME)
FROM color c
ORDER BY name

Output:

id          NAME                      
----------- ------------------------- -----------
4           blue                      1
1           orange                    2
3           red                       3
2           yellow                    4

I didn't understand how this query generates row_nums without using row_number function? How the query flow would be when subquery will be executed before order by or after?

Aaron Bertrand · Accepted Answer · 2014-01-17 16:51:03Z

5

It's a correlated subquery. For each row in c, count the number of rows in the same table, with the same name or a lower name.

This is an absolutely inefficient and wasteful way to generate a row number, but is likely the most common way people worked around the lack of such a function in, say, SQL Server 2000. Today, of course, you should be using:

SELECT id, name, ROW_NUMBER() OVER (ORDER BY name)
  FROM dbo.color
  ORDER BY name;

Assuming name is unique and you don't need to break ties.

answered Jan 17, 2014 at 16:51

Aaron Bertrand

282k37 gold badges469 silver badges468 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Registered User Over a year ago

How would i break ties?

PinnyM Over a year ago

@RegisteredUser - however you wish :) Adding an id to the ORDER BY clause is a common method.

Aaron Bertrand Over a year ago

@RegisteredUser It depends on what you mean, since ties can come in many varieties. If name is not unique, then you can choose another column to order by after name, for example OVER (ORDER BY name, id) would give 4,blue a row number of 1, and 6,blue a row number of 2.

Brett Schneider · Accepted Answer · 2014-01-17 16:50:10Z

2

the subselect

select count(1) from color i where i.name <= c.name)

counts the rows where the name is smaller or equal to the name in the current row of the outer select, thus counting the rows up to that point. you can confuse it by having a colour twice in that table. try inserting red again and see what happens

answered Jan 17, 2014 at 16:50

Brett Schneider

4,1032 gold badges19 silver badges33 bronze badges

Comments

PinnyM · Accepted Answer · 2014-01-17 16:50:07Z

1

It's using a correlated subquery to count the number of rows above it. Note that for large datasets, this query will be vastly less performant than using ROW_NUMBER(), which uses fixpoint logic to determine its place instead of running COUNT for each row.

answered Jan 17, 2014 at 16:50

PinnyM

35.6k4 gold badges77 silver badges84 bronze badges

16 Comments

swasheck Over a year ago

FYI: "Performant" is a noun - someone who performs something capably.

PinnyM Over a year ago

@swasheck: This doesn't appear to be the general understanding - en.wiktionary.org/wiki/performant

Aaron Bertrand Over a year ago

Why not just use a word that conveys the same meaning (like efficient) without any of the controversy (or causing people to bang their head on their desk)? This is like using utilize instead of use, or myriad instead of many, except that performant is still not in the dictionary. english.stackexchange.com/questions/38945/… The key is, when you choose to use that word, which many people believe is not a word, they may think less of you. Here, who cares; in a business environment, use caution. IMHO.

swasheck Over a year ago

@PinnyM that's the link i looked at too which notes that it's a noun. "performant" isn't in the dictionary at all.

PinnyM Over a year ago

@swasheck - is Oxford Dictionary an acceptable resource? oxforddictionaries.com/us/translate/french-english/performant

|

Collectives™ on Stack Overflow

row_nums without using row_number function?

3 Answers 3

3 Comments

Comments

16 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

16 Comments

Your Answer

Sign up or log in

Post as a guest

Related