Selecting the nth record in a table throug a single Querry

Question

Selecting the nth record in a table throug a single Querry

Tarundeep Singh Sodhi

Newbie

Points: 2
More actions
May 22, 2004 at 5:22 am

#161923

Hello
I have a problem for whivh i require a single querry,plzz try to help.
In postgres or My SQl there are commands caled as limit and offset
Eg
Select cust_no from customer limit 2 offset 5
this will return the 6th and 7th row from the cutomer table
i need to know if there is anything simmilar which i can use in SQL server
plzz any idea that comes to u r mind is welcome
thanks

Viewing 4 posts - 1 through 3 (of 3 total)

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

Wayne Lawton Hall of Fame Points: 3532 More actions · Answer 1

I am not sitting in front of my server at the moment, so the syntax may be a bit off. However, here is a method. Basically, use a derived query to get the initial short list, then select the last N rows from the derived query, similar to the following code:

select top 2 derived.cust_no from (select top 7 cust_no from customer order by cust_no asc) derived order by cust_no desc

hoo-t SSChampion Points: 12713 More actions · Answer 2

Its important to realize that in SQL Server, there really is no 6th or 7th specific record. The storage order is completely arbitrary. Wayne used an "order by" in his query, which will give you the 6th and 7th records when sorted by cust_no. While this makes sense, it would not make sense to issue the same query without the "order by" clause.

Steve

Tarundeep Singh Sodhi Newbie Points: 2 More actions · Answer 3

Hi guys

Thanks for the replies,as steve said there really is no 6th and 7th specific record in sql server,but unfortunately my problem remains. you see, we can use the Order By clause for a single column but when i try for composite primary keys this operation fails,Sql server fails to give me a proper response,the same row repeatedly gets selected, eg

String_Col1 String_Col2 Numeric_Col

a a 12

b b 12

and so on then sometimes i find that even by changeing the top clause i still get the same row as result,so can u guys tell me why this is happening, or may be give me a different solution.

Thanks