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?
-
2Why can't you use ROW_NUMBER (Transact-SQL)? Is this for SQL Server 2000?Mikael Eriksson– Mikael Eriksson2013-09-17 13:57:03 +00:00Commented Sep 17, 2013 at 13:57
Add a comment
|
4 Answers
select *, (
select count(*)
from prd p2
where p1.prdid >= p2.prdid
) as cnt
from prd p1
Comments
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
1 Comment
Pawan Pillai
I think this should be the best answer. Thanks!
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
Comments
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.
