ROW_NUMBER替代SQL Server 2000

RIGHT now I'm using ROW_NUMBER() in my procedure in SQL Server 2008 as follows:

现在我在SQL Server 2008的程序中使用ROW_NUMBER（），如下所示：

WITH cars as(SELECT carid,mileage,retailprice,imageurl,model,year, 
             Zips.Distance AS Miles, Manufacturers.mfgName as Make,
             dealers.companyname as companyname, CASE @sortby 
WHEN 'D' THEN ROW_NUMBER() OVER (ORDER BY  Manufacturers.mfgName) 
WHEN 'P' THEN ROW_NUMBER() OVER (ORDER BY retailprice) 
WHEN 'M' THEN ROW_NUMBER() OVER (ORDER BY mileage) 
END as 'rownum'
FROM usedcars INNER JOIN #TempZips Zips ON Zips.ZipCode =usedcars.loczip
left join Manufacturers on Manufacturers.mfgid=usedcars.mfgid 
left join dealers on dealers.dealerid = usedcars.dealerid 
where usedcars.active=1 and usedcars.dealerid=@dealerid)
select @totalrecords as totalrec,*  from cars 
where rownum between @skip and  @take

Is there an equivalent to this that works in SQL2000? (ROW_NUMBER wasn't introduced until SQL2005).

在SQL2000中是否有相同的功能？（直到SQL2005才引入ROW_NUMBER）。

1 个解决方案

#1

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

您可以使用标识列创建临时表，并将数据插入其中。

Then use the temp table.

然后使用临时表。

#1