Thread: Equivalent of Limit in MySql

Hello
I need to do paging, but cant seem to able to get query right in Sql Server 2000/2005

In MySql, it was simple, just used Limit

Anything like that in Sql Server or is it complicated here??

Thanx

Originally Posted by micky

Hello
I need to do paging, but cant seem to able to get query right in Sql Server 2000/2005

In MySql, it was simple, just used Limit

Anything like that in Sql Server or is it complicated here??

Thanx

M, if you have SQL Server 2005 or higher you can do something like this:

Code:

SELECT * FROM
(
    SELECT
         [column1]
        ,[column2]
        ,[column3]
        ,ROW_NUMBER() OVER (ORDER BY [column2] DESC) AS RowNum
    FROM table_name
) TableName
WHERE TableName.RowNum BETWEEN (@PageIndex*@PageSize) AND ((@PageIndex+1) *@PageSize)
ORDER BY [column2] DESC

Also, if your table happens to have a Primary Key which is an auto increment value:

Code:

Select Top (@pagesize) *
   From Table
   Where keyColumn > (@pageindex * @pageSize)

might work. Assuming no gaps in the sequence and you don't have other selection criteria. (i.e. this would work on a Report Cache table for example)

Hmm, i saw that in some links......... kind of complicated with my query

Ok, i'll try it tomorrow and get back

M, just be sure that you do all your logic in the inner query:

Code:

    SELECT
         [column1]
        ,[column2]
        ,[column3]
        ,ROW_NUMBER() OVER (ORDER BY [column2] DESC) AS RowNum
    FROM table_name

Leave the outer query alone and it shouldn't be a problem.

J, i have a query like this

Code:

strSQL = "SELECT T.Team_Id, T.Team_Name, SP.Sport_Name, S.School_Name, C.Class_Name, D.District_Name, ST.State_Name, CT.City_Name " & _
"FROM tblTeam as T, tblSport as SP, tblSchool as S, tblClass as C, tblDistrictMaster as D, tblStateMaster as ST, tblCityMaster as CT " & _
"WHERE T.Sport_Id=SP.Sport_Id and T.School_Id=S.School_Id and S.State_Id=ST.State_Id And S.City_Id=CT.City_Id " & _
"and S.Class_Id=C.Class_Id and S.District_Id=D.District_Id  ORDER BY T.Team_Name"

I need to show paging on this query

ok, i got it working

Code:

Select * From
(SELECT T.Team_Id, T.Team_Name, SP.Sport_Name, S.School_Name, C.Class_Name, D.District_Name, ST.State_Name, CT.City_Name, 
ROW_NUMBER() OVER (ORDER BY Team_Name, School_Name) AS RowNum
FROM tblTeam as T, tblSport as SP, tblSchool as S,
tblClass as C, tblDistrictMaster as D, tblStateMaster as ST, tblCityMaster as CT
WHERE T.Sport_Id=SP.Sport_Id and T.School_Id=S.School_Id and S.State_Id=ST.State_Id And S.City_Id=CT.City_Id
and S.Class_Id=C.Class_Id and S.District_Id=D.District_Id) 
TableName WHERE TableName.RowNum > 0 and TableName.RowNum <= 50 

Thanx J