CODEWITHSUNDEEP
sqlsql-server
Registered User
Registered User

Reputation: 1564

row_nums without using row_number function?

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?

Upvotes: 0

Views: 852

Answers (3)

anon
anon

Reputation:

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.

Upvotes: 5

Brett Schneider
Brett Schneider

Reputation: 4103

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

Upvotes: 2

PinnyM
PinnyM

Reputation: 35533

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.

Upvotes: 1

Related Questions