We can get records 30-60 (30 records) in My SQL " SELECT * FROM `myTable` LIMIT 30 , 30 ".  How can i do thisin SQL Server 2000 and 2005 ?

Harshana

---

Hi Harshana,

There is no function available with SQL Server similar to "LIMIT" but you can get the result set as you want.

Here is one the ways of limiting records with SQL Server 2000.
SELECT  T.*
FROM
(
    SELECT TOP 100 PERCENT (SELECT COUNT(*) FROM Person.Contact WHERE ContactID <= c.ContactID) AS RowNumbr, c.*
    FROM Person.Contact c
    ORDER BY c.ContactID
) T
WHERE T.RowNumbr BETWEEN 30 AND 60

You get the same easily with SQL Server 2005 by using a ranking function.
SELECT *
FROM
(
    SELECT ROW_NUMBER() OVER (ORDER BY ContactID) AS RowNumber, *
    FROM Person.Contact) T
WHERE T.RowNumber BETWEEN 30 AND 60

Hope these solutions work for you. Let us know if you have more queries.

Thank you Very much.

Harshana 

---

Alternatively,  you can try this too.  It works better for SQL 2000, where the ranking function provides a better emthod in sql 2005.

SELECT TOP 30 *

FROM Person.Contact

WHERE ContactID >

ALL(

SELECT TOP 30 ContactID

FROM Person.Contact

ORDER BY ContactID

)

---

G.R.Preethiviraj Kulasingham MCITP: DBA
http://preethiviraj.blogspot.com/
Plan Your Work and Work Your Plan!

SELECT * FROM
(
              SELECT *, ROW_NUMBER()over (ORDER BY EmployeeID)as row
                 FROM
                     Employees
)as Temp
WHERE
row > 30 and row <= 60

Interestingly today we found that on certain situations the following query worked better than the query I submitted.  (SQL 2000).  It is not always, but there is a difference at times.  I have not explored the reasons yet.  but a heads up so that you all can start a research on the best method

SELECT * FROM (

   SELECT TOP 30 * FROM

        (SELECT TOP 60 * FROM Person.Contact ORDER BY ContactID) A

   ORDER BY ContactID DESC

) B ORDER BY ContactID

If you don’t want the rows to be ordered (I need only the rows from 30 -60, but on any order) you can remove the first and last rows. You may get the rows in reverse order (60, 59, 58 ... etc.)

 

---

G.R.Preethiviraj Kulasingham MCITP: DBA
http://preethiviraj.blogspot.com/
Plan Your Work and Work Your Plan!