nth row

Question

Post reply

nth row

bruce_reid

SSCrazy

Points: 2419
More actions
April 8, 2003 at 12:35 pm

#158867

How to pick the nth row in a table..i,e 5th,7th,24th,100th....
I wanted to pick a row of my choice.Can anyone help me in this
Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

bp SSCarpal Tunnel Points: 4656 More actions · Answer 1

USE NORTHWIND

SELECT TOP 1 *

FROM (

SELECT TOP N * FROM Employees ORDER BY EmployeeID ASC

) X ORDER BY EMPLOYEEID DESC

where N is the ordinal position of the record you want to select.

HTH

Billy

bruce_reid SSCrazy Points: 2419 More actions · Answer 2

Thanks for ur response.

I have only one column and that is of varchar data type.That is the main problem.SO can u tell me how to do this for varchar data type.

Rodrigo Acosta SSC-Insane Points: 21261 More actions · Answer 3

Another way is:

CREATE TABLE #tmp_Table

(Pos INT IDENTITY (1,1) not null,

c1 datatype,

c2 datatype,

c3 datatype)

INSERT INTO #tmp_Table

SELECT * FROM Yourtable

In here you have your table but with a column name col with has the order of the row.

Then you can SELECT from your table where Pos = what ever you need.

bruce_reid SSCrazy Points: 2419 More actions · Answer 4

No,I have only one column(name) of varchar data type.I wanted to get nth row in this table or I wanted to get the rows between the values M and N.M and N can of any integer value.

Thanks

bp SSCarpal Tunnel Points: 4656 More actions · Answer 5

VARCHAR can be sorted (unless there is some other way that defines the sort order of that table). In your case, you will be sorting by NAME alphabetically.

If you want between M and N then...

SELECT TOP (N minus M plus 1) *

FROM (

SELECT TOP N * FROM Employees ORDER BY lastname ASC

) X ORDER BY lastname DESC

bruce_reid SSCrazy Points: 2419 More actions · Answer 6

Hey i dont want to sort the values,just i want to pick the nth row,not nth max or nth min,i want to get the value in the 10th row in a table of 100 rows.I dont mind whether the 10th value is min or max.just i wanted to get the value in the 10th row.

Thanks

bp SSCarpal Tunnel Points: 4656 More actions · Answer 7

No sorting allowed?? ok, then, how about loading the table into a temp table with an identity column and sorting it based on newly created identity column.

Try....

use northwind

DECLARE @tempTable TABLE(THE_ID INT IDENTITY(1,1), LASTNAME NVARCHAR(20))

INSERT INTO @tempTable(LASTNAME) SELECT LASTNAME FROM EMPLOYEES ORDER BY 1 DESC

SELECT TOP (N minus M plus 1) LASTNAME

FROM (SELECT TOP N THE_ID, LASTNAME FROM @tempTable ORDER BY THE_ID ASC) X

ORDER BY THE_ID DESC

HTH

Billy