SQL Query: How can I get data of row with number 1000 direclty?

1
votes

If I have a SQL Table called Persons that contain about 30000 rows and I want to make a SQL query that retrieve the data of row number 1000 ... I got it by non professional way by making the following query 

Select Top 1 * from 
(
    Select top 1000 *
    From Persons
    Order By ID 
)A
Order By A.ID desc

But I feel that's a more optimized query that can do that ... can any lead me to perfect query ?
Note : table contain PK column called "ID" but it's not sequential

3
sql-server-2005sql-server-2008-r2row-number

3 Answers

3
votes

row_number is the best approach but as you only want a single row be sure to look at the plan. It might turn out better to identify the desired row then join back onto the original table to retrieve additional columns.

WITH T1
     AS (SELECT *,
                ROW_NUMBER() OVER (ORDER BY number) AS RN
         FROM   master..spt_values)
SELECT name,
       number,
       type,
       low,
       high,
       status
FROM   T1
WHERE  RN = 1000;

Gives

Table 'spt_values'. Scan count 1, logical reads 2005

CPU time = 0 ms, elapsed time = 19 ms.

plan 1

WITH T2
     AS (SELECT number,
                type,
                name,
                ROW_NUMBER() OVER (ORDER BY number) AS RN
         FROM   master..spt_values)
SELECT TOP 1 C.name,
             C.number,
             C.type,
             C.low,
             C.high,
             C.status
FROM   T2
       CROSS APPLY (SELECT *
                    FROM   master..spt_values v
                    WHERE  v.number = T2.number
                           AND v.type = T2.type
                           AND ( v.name = T2.name
                                  OR ( v.name IS NULL
                                       AND T2.name IS NULL ) )) C
WHERE  RN = 1000;

Gives

Table 'spt_values'. Scan count 1, logical reads 7

CPU time = 0 ms, elapsed time = 1 ms.

Plan 2

1
votes

In SQL Server 2005+ you can use the following:

WITH MyCte AS 
(
    SELECT
        [CategoryId]
        ,[CategoryName]
        ,[CategoryDescription]
        ,ROW_NUMBER() OVER (ORDER BY CategoryId ASC) AS RowNum
    FROM
        [Carmack].[dbo].[job_Categories]
)
SELECT *
FROM    MyCte
WHERE   RowNum = 3
0
votes

you can try this

select * from Person P
where 999 = ( select count(id) from Person P1 , Person P2 
              where P1.id = P.id and P2.id < P1.id)