Warnar Boekkooi

No LIMIT in MSSQL how to do the same

Hey all,

I was having the small problem with a site with alot of records in the DB and well i pulled them out every time the site was shown but it was only showing 10 records not all.
The problem was that i could use the same SQL as in ASP.NET DataGrid Paging Part 2 - Custom Paging because i neede couldn't use ID's that way.

So i neede to get 10 records sort the data and put it on the site :) here is how to get 10 records from the database:
SELECT * FROM KK.dbo.brand WHERE brandID IN(SELECT TOP 10 brandID FROM(SELECT TOP @Rows brandID FROM KK.dbo.brand ORDER BY brandID) A ORDER BY brandID DESC) ORDER BY brandID

Ok what are we doing here?

We are first getting the top x rows witch in my case is a int called @Rows and order them with the brandID (SELECT TOP @Rows brandID FROM KK.dbo.brand ORDER BY brandID)

Second we take that info and turn it around and ask for the top 10 records so now we have the record we need but up site down (SELECT TOP 10 brandID FROM(SELECT TOP @Rows brandID FROM KK.dbo.brand ORDER BY brandID) A ORDER BY brandID DESC)

So last think to do is turn and turn all around :D and that's what we do last and then you get:
SELECT * FROM KK.dbo.brand WHERE brandID IN(SELECT TOP 10 brandID FROM(SELECT TOP @Rows brandID FROM KK.dbo.brand ORDER BY brandID) A ORDER BY brandID DESC) ORDER BY brandID

Cool aint it?
Dues anyone know if LIMIT will be in the new mssql?

Happy Netting,
Warnar