CODEWITHSUNDEEP
sql-server
satyajit
satyajit

Reputation: 2700

How do I generate row number without using rownum() in sql server

I have the following table:

CREATE table prd
(
prdid varchar(10)
)
insert into prd values ('prd1011'),('prd1023'),('prd4532'),('prd2341')

I need to return the same with rownumber without using ROWNUM() built in function. Is that possible?

Upvotes: 2

Views: 28680

Answers (4)

Martin Smith
Martin Smith

Reputation: 453288

You've failed to specify SQL Server version or motivation for the request.

A SQL Server 2012+ method

SELECT prdid,
       COUNT(*) OVER (ORDER BY prdid ROWS UNBOUNDED PRECEDING)
FROM prd

SQL Fiddle

Upvotes: 4

GriGrim
GriGrim

Reputation: 2921

Solution for non-unique column:

SELECT p.prdid, p.num - t.n + 1 as num
FROM (
    SELECT p1.prdid, p1.cnt
        , (
            SELECT COUNT(*)
            FROM prd p2
            WHERE p2.prdid <= p1.prdid
        ) num
    FROM (
        SELECT p1.prdid, COUNT(*) cnt
        FROM prd p1
        GROUP BY p1.prdid
    ) p1
) p
INNER JOIN (
    select 1 n union all select 2 union all
    select 3 union all select 4 union all select 5 union all
    select 6 union all select 7 union all select 8 union all
    select 9 union all select 10 union all select 11 union all
    select 12 union all select 13 union all select 14 union all
    select 15 union all select 16 union all select 17 union all
    select 18 union all select 19 union all select 20
) t ON t.n <= p.cnt

Found here.

Upvotes: 2

satyajit
satyajit

Reputation: 2700

I tried this and it worked but it is working only if the prdid column is unique:

select prdid,
(select COUNT(*) from prd p where p.prdid >= r.prdid)rnk
from prd r order by rnk

Upvotes: 2

GriGrim
GriGrim

Reputation: 2921

select *, (
    select count(*)
    from prd p2
    where p1.prdid >= p2.prdid
) as cnt
from prd p1

Upvotes: 4

Related Questions