Friday, June 1, 2012

Microsoft SQL Server 2000:Create Row number into select query

Mention TableName and Primary key value in Below Query.

SELECT ( SELECT sum(1) FROM TableName T1
WHERE  T1.PrimartKey <= T2.PrimaryKey
) AS [Row Number]
,*
FROM
TableName T2

Go 

Use of Partition By.

SELECT *
  FROM (
    SELECT  (
      SELECT COUNT(*)
        FROM #temp AS counter
      WHERE counter.meterid = t.meterid --partiton key
        AND  counter.serial >= t.serial--order by key(identity column)
      ) AS rowNumber,*
    FROM #temp t
  ) AS r1

where rownumber between 1 and 10