Row_Number Simulation in SQL Server 2000

Row_Number simulation in Sql server 2000

Use this query:

SELECT t1.name, t.n
FROM
(
    SELECT a.name, a.c, (SELECT COUNT(*) FROM @input AS i2 WHERE i2.Name <= a.Name) [rn]
    FROM
    (
        SELECT i.name, count(*) c
        FROM @input i
        GROUP BY i.name
    )a
)t1
JOIN @t t ON t.n <= t1.rn
WHERE t.n > t1.rn - t1.c 

It produces desired output:

name     n
-------- -----------
Aryan    1
Aryan    2
Aryan    3
Aryan    4
Jaesmin  5
Jaesmin  6
Jaesmin  7
Joseph   8
Joseph   9
Marya    10
Padukon  11
Padukon  12
Vick     13
Vicky    14
Vicky    15
Vicky    16

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

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

SQL Server pagination of a result set

create procedure ProcWrap
as

declare @T table (ID int, Name nvarchar(50))

insert into @T
exec ProcToWrap

select *
from @T
where ID < 10

Edit 1
Don't have SQL Server 2000 to test on and I don't remember if table variables where available then. Here is a procedure using a temp table instead. Added a RowNum identity column that you can use for pagination.

create procedure ProcWrap2
as

create table #T (RowNum int identity, ID int, Name nvarchar(50))

insert into #T
exec ProcToWrap

select *
from #T
where RowNum between 10 and 19

drop table #T

Edit 2
Output from ProcToWrap in this case is columns ID and Name. RowNum is generated automatically.

Stick two tables together

Assuming that the id columns are unique and not null, you can "zip" your tables by:

1. Creating a row number for each row that corresponds to the
   position of the row when the table is ordered by the unique id (as
   polishchuk mentioned in his comment); and,
2. Simulating a FULL OUTER JOIN with 2 LEFT OUTER JOINS.

To demonstrate, I used two tables with differing row counts:

CREATE TABLE foo (id INTEGER PRIMARY KEY AUTOINCREMENT, data TEXT);
INSERT INTO foo VALUES (NULL, 'a');
INSERT INTO foo VALUES (NULL, 'b');
INSERT INTO foo VALUES (NULL, 'c');
INSERT INTO foo VALUES (NULL, 'd');
INSERT INTO foo VALUES (NULL, 'e');
INSERT INTO foo VALUES (NULL, 'f');
INSERT INTO foo VALUES (NULL, 'g');
INSERT INTO foo VALUES (NULL, 'h');
INSERT INTO foo VALUES (NULL, 'i');
INSERT INTO foo VALUES (NULL, 'j');
DELETE FROM foo WHERE data IN ('b', 'd', 'f', 'i');

CREATE TABLE bar (id INTEGER PRIMARY KEY AUTOINCREMENT, data TEXT);
INSERT INTO bar VALUES (NULL, 'a');
INSERT INTO bar VALUES (NULL, 'b');
INSERT INTO bar VALUES (NULL, 'c');
INSERT INTO bar VALUES (NULL, 'd');
INSERT INTO bar VALUES (NULL, 'e');
INSERT INTO bar VALUES (NULL, 'f');
INSERT INTO bar VALUES (NULL, 'g');
INSERT INTO bar VALUES (NULL, 'h');
INSERT INTO bar VALUES (NULL, 'i');
INSERT INTO bar VALUES (NULL, 'j');
DELETE FROM bar WHERE data IN ('a', 'b');

To obtain a more readable output, I then ran:

.headers on
.mode column

Then you can execute this SQL statement:

SELECT COALESCE(id1, -1) AS id1, data1, 
       COALESCE(id2, -1) as id2, data2 
FROM (
    SELECT ltable.rnum AS rnum, 
           ltable.id AS id1, ltable.data AS data1, 
           rtable.id AS id2, rtable.data AS data2
    FROM
        (SElECT (SELECT COUNT(*) FROM foo 
            WHERE id <= T1.id) rnum, id, data FROM foo T1
        ) ltable
        LEFT OUTER JOIN
        (SElECT (SELECT COUNT(*) FROM bar 
            WHERE id <= T1.id) rnum, id, data FROM bar T1
        ) rtable
        ON ltable.rnum=rtable.rnum
    UNION
    SELECT rtable.rnum AS rnum, 
           ltable.id AS id1, ltable.data AS data1, 
           rtable.id AS id2, rtable.data AS data2
    FROM
        (SElECT (SELECT COUNT(*) FROM bar 
            WHERE id <= T1.id) rnum, id, data FROM bar T1
        ) rtable
        LEFT OUTER JOIN
        (SElECT (SELECT COUNT(*) FROM foo 
            WHERE id <= T1.id) rnum, id, data FROM foo T1
        ) ltable
        ON ltable.rnum=rtable.rnum)
ORDER BY rnum

Which gives you:

id1         data1       id2         data2     
----------  ----------  ----------  ----------
1           a           3           c         
3           c           4           d         
5           e           5           e         
7           g           6           f         
8           h           7           g         
10          j           8           h         
-1                      9           i         
-1                      10          j    

This works "both ways", for example, if you invert the two tables (foo and bar), you get:

id1         data1       id2         data2     
----------  ----------  ----------  ----------
3           c           1           a         
4           d           3           c         
5           e           5           e         
6           f           7           g         
7           g           8           h         
8           h           10          j         
9           i           -1                    
10          j           -1                  

---

Related Topics