SQL Server Row_Number() on 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 to get the current row number in an SQL Server 2000 query?

You can always try to use a temp table with an identity column

DECLARE @table TABLE(
        [id] INT IDENTITY(1,1),
        Val VARCHAR(10)
)

DECLARE @TableFrom TABLE(
        Val VARCHAR(10)
)
INSERT INTO @TableFrom (Val) SELECT 'A'
INSERT INTO @TableFrom (Val) SELECT 'B'
INSERT INTO @TableFrom (Val) SELECT 'C'
INSERT INTO @TableFrom (Val) SELECT 'D'

INSERT INTO @table (Val) SELECT * FROM @TableFrom ORDER BY Val DESC
SELECT * FROM @table

Some of the best paging i have seen in Sql Server 2000 uses this pattern

DECLARE @PageStart INT,
        @PageEnd INT

SELECT  @PageStart = 51,
        @PageEnd = 100

SELECT  <TABLE>.*
FROM    (
            SELECT  TOP (@PageStart - 1)
                    <ID>
            FROM    (
                        SELECT  TOP (@PageEnd)
                                <ID>
                        FROM    TABLE
                        ORDER BY <ID> ASC
                    ) SUB
            ORDER BY SUB.<ID> DESC
        ) SUB INNER JOIN
        <TABLE> ON SUB.<ID> = <TABLE>.<ID>
ORDER BY SUB.<ID>

Modify SQL query with Row_Number() and partition functions for SQL Server 2000

 select m.*
 from machinelist as m
        inner join (
               select
                     t.machine_id, max(t.version) as version
               from machinelist as t
               group by t.machine_id
        ) as mm on
              mm.machine_id = m.machine_id and mm.version = m.version

Instead of ROW_NUMBER ORDER BY

To recreate the partition by EmpId, your subquery should have l.EmpId = m.Empid. You really need a unique column or set of columns to unique identify a row for this version to work properly. In an attempt based on the given data, if EmpId, Amt are a unique pair you can use and l.Amt < m.Amt. If you have a surrogateid on the table, that would be better instead of Amt.

select
    k.id
  , k.[Name]
  , m.Amt
  , ( select count(*)
      from EmpIncrement l
      where l.Empid = m.Empid
        and l.Amt <= m.Amt
  ) as RowNum
from EmpIncrement m
  inner join Employee k
    on m.Empid = k.id

If you have no set of columns to uniquely identify and order the rows, you can use a temporary table with an identity() column.

create table #temp (tmpid int identity(1,1) not null, id int, [Name] varchar(32), Amt int);
insert into #temp (id, [Name], Amt);
select
    k.id
  , k.[Name]
  , m.Amt
from EmpIncrement m
  inner join Employee k
    on m.Empid = k.id;

select 
    t.id
  , t.[Name]
  , t.Amt   
  , ( select count(*)
          from #Temp i
          where i.Empid = t.Empid
            and i.tmpId <= t.tmpId
      ) as RowNum
from #temp t

ROW_NUMBER Alternative for SQL Server 2000

You can create a temp table with an identity column, and insert your data into that.

Then use the temp table.

SQL 2000 Row numbering with GROUP BY

Following script should get you started. In a nutshell, the script

• creates a temp table, adding an IDENTITY column to serve as rownumber
• inserts your original data into the temp table
• uses MIN(Rownumber) for each ID to get an offset.
• JOIN the temp table with the calculated offsets to restart the count for each group.

SQL Statement

CREATE TABLE #TempTable (Rownumber INTEGER IDENTITY(1, 1), ID INTEGER)
SET IDENTITY_INSERT #TempTable OFF

INSERT INTO #TempTable
SELECT      *
FROM        YourOriginalTable
ORDER BY    ID, int_Sort

SELECT    t.ID, t.Rownumber, t.Rownumber - o.Offset
FROM      #TempTable t
          INNER JOIN (
            SELECT    ID, MIN(Rownumber) - 1 AS Offset
            FROM      #TempTable
            GROUP BY  ID
          ) o ON o.ID = t.ID            

DROP TABLE #TempTable

Test script

DECLARE @YourTable TABLE (ID VARCHAR(1))
CREATE TABLE #TempTable (Rownumber INTEGER IDENTITY(1, 1), ID INTEGER)

SET IDENTITY_INSERT #TempTable OFF
INSERT INTO @YourTable (ID) VALUES (1)
INSERT INTO @YourTable (ID) VALUES (1)
INSERT INTO @YourTable (ID) VALUES (1)
INSERT INTO @YourTable (ID) VALUES (1)
INSERT INTO @YourTable (ID) VALUES (1)
INSERT INTO @YourTable (ID) VALUES (2)
INSERT INTO @YourTable (ID) VALUES (2)
INSERT INTO @YourTable (ID) VALUES (2)
INSERT INTO @YourTable (ID) VALUES (2)

INSERT INTO #TempTable
SELECT      *
FROM        @YourTable
ORDER BY    ID

SELECT    t.ID, t.Rownumber, t.Rownumber - o.Offset
FROM      #TempTable t
          INNER JOIN (
            SELECT    ID, MIN(Rownumber) - 1 AS Offset
            FROM      #TempTable
            GROUP BY  ID
          ) o ON o.ID = t.ID            

DROP TABLE #TempTable

Eliminating ROW_NUMBER() for SQL 2000

Using a temp table with an identity column to simulate the ROW_NUMBER may be your best bet performance wise:

CREATE TABLE #tmpRowNum (
    ROWID INT IDENTITY(1,1),
    ID INT
)

INSERT INTO #tmpRowNum
    (ID)
    SELECT ID
        FROM [ARAS].[ARAS].[Movement]
        ORDER BY InstallmentNumber, ID

---

Related Topics