MS SQL Server 2000/2003/2008

Databases
1

Hi

A simple yet weird question for MS SQL Server 2000/2003/2008 users.

I have a table with 100 rows and there is NO auto-id number in that table. How do I fetch the x number of records from nth location?

Thanks

2

Thats my query

select firstname, lastname from employee
3

there is no “nth location”

rows in a database table have ~no~ sequence

4

ok lets make this this way

What if I want to fetch rows from 20th record to 30th?

5

you cannot

the rows aren’t numbered

6

In mysql we do it with LIMIT 20,30 so there must be something similar to it in MS SQL?

7

in neither mysql nor sql server does it make any sense to select the 21st through 51st row without an ORDER BY clause

8

Sql 2005 or better can do this using ROWNUMBER. Before that, you are looking at lots of voodoo. If there are only 100 rows, I’d handle this stuff on the client side.

9

Hi

Thanks for the help.

Hw do we use ROWNUMBER in our query?

Thanks

10

that depends :slight_smile:

can we see your query please

11

what sequence would you like to show the employees in?

12 
order by firstname
13 
SELECT firstname
     , lastname 
     , ROW_NUMBER() 
         OVER ( ORDER BY firstname ) AS rn
  FROM employee
ORDER 
    BY firstname
14

But where did you mention that display only records from 20th to 30th?

15 
SELECT *
  FROM (
       SELECT firstname
            , lastname 
            , ROW_NUMBER() 
                OVER ( ORDER BY firstname ) AS rn
         FROM employee
       ) AS d
 WHERE rn between 20 and 30
ORDER 
    BY firstname