We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

how to get nth record in sql server

mahajan344
mahajan344 asked
on
Medium Priority
575 Views
Last Modified: 2012-05-07
i have a table ab
having column abc
like

id abc
1   20
2    50
3    30
4   50
5   60

i wanna get record no 2 or 3 any any no... without using cursors, declare function..
how can i get this
Comment
Watch Question

Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
what version of sql?
what is the "order" you want to apply to determined the "nth" record?

Author

Commented:
sql server 2005..
its just demo..
actually i just wanna get 5th, 4th any record in the table.. without using cursors,and declre function..
wont this work?

select * from table name where id between 2 and 3
pls. use rank over.
or you may use row_number or rank function if you have specific condition for selection.

Author

Commented:
dude how to use rank over function please mention...its very urgent..
you didn't answer AngelIII's question so it is bit confusing to tell you about RANK function, even have a look.


select id,abc from
(
select id,abc, rank() over(partition by abc order by id) as r from tablename
) as t

--or

select id,abc from
(
select id,abc, rank() over(partition by abc order by id) as r from tablename
) as t where r between 2 and 3

Author

Commented:
no dude i don't waana use between.. its very simple with between..
actully i have a table with one column that is name..
and i have many name that column..

so i wanna get any record...how do i get that..i don't have id.. i just have only one column..
select name from
(
select name, row_number() over(partition by name order by name) as r from tablename
) as t where r between 2 and 3

or may be this one too:

select name from
(
select name, rank() over(partition by name order by name) as r from tablename
) as t where r between 2 and 3
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
well to get the 5th record (order by the name column values):
select name from
(
select name, row_number() over(order by name) as r 
  from tablename
) as t
where r = 5

Open in new window

as I was in hurry, you don't need partition by, just need order by, have a look

select name from
(
select name, row_number() over(order by name) as r from tablename
) as t where r between 2 and 3

Author

Commented:
no dude i have only name.. but i wanna retrieve 4,5 any record...
without using asc or desc order...
chk the attachment image....
12.jpg

Author

Commented:
i just wanna retrieve any record... ??
any 1st,2nd or 3rd record.. without using asc ,desc order.. can it be possible ???
its very very urgent...
i will be very thankfull..if anyone can solve this..
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
>without using asc or desc order...
not really possible, resp not reliable

but you can try this
select name from
(
select name, row_number() over(order by 1) as r 
  from tablename
) as t
where r = 5

Open in new window

Author

Commented:
its giving error..
Windowed functions do not support integer indices as ORDER BY clause expressions
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
can you please explain why you need "without order" ?

Author

Commented:
cause i don't have two column .. i have only one column that is name having datatype varchar...
so i can't apply order by to that column...
so please how can i get any record... from that table.
any record.. 2nd, 3rd......
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
let me reformulate:

I presume you did a SELECT * FROM yourtable (no order by), and see (for example) 10 records.
you want to get the 5th, which is, in your understand, the 5th in the order the rows are stored in the table.

unfortunately, there IS no such "order" guarantee, as any insert/delete/update can change that "order".
so, if 2 users would run such a query to get row number #5, both could get a different result.

which is what I try to find out: if you are trying to rely on the "row order", you are building a system with a BIG flaw.
and in that case, you HAVE to change something in the "logic"

please clarify

Author

Commented:
i agree with you dude...
but you can use any function,cursors,trigger,anything..
but don't use insert,create,,,or any other table..


is that possible...
then display any record.. 2nd,3rd,.. any.. i think its is possible..

isn't it.. dude..

Author

Commented:
please tell me is it possible ???
using cursors,trigger,anything but don't use..
insert,create, or any table..
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
mahajan344:
asking for nth record without order by is  NOT the correct method in sql. your logic MUST have a flaw.
you are asking for something that, even if possible, is flawed.

Author

Commented:
you can use order by but "i have only one column which contains name" then how can i order by name ??
it will not give correct output ??
can't it be possible with triggers,cursors, or any function ??
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
give nth record, with "order by the name", has already been given:
select name from
(
select name, row_number() over(order by name) as r 
  from tablename
) as t
where r = 5

Open in new window

Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
>can't it be possible with triggers,cursors, or any function ??
the problem is that you need some order. it is not possible without "order".

of course, you could do below, BUT that will need to read the entire table every time:
declare @t table (id int identity, name varchar(1000))
set nocount on
insert into @t (name) select name from YOUR_TABLE_NAME
select name from @t where id = 5

Open in new window

Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
and you could put that into a function

and I repeat: that approach is : BAD BAD VERY BAD BAD
create function dbo.GetNthName(@n bigint)
RETURNS varchar(100)
AS
BEGIN
declare @t table (id int identity, name varchar(1000))
declare @res varchar(100)
set nocount on
insert into @t (name) select name from YOUR_TABLE_NAME
select @res = name from @t where id = @n
return @res
END
 
 
usage would be:
 
select dbo.GetNthName(5)

Open in new window

Author

Commented:
dude soory i can't create,insert into another table..:(
any way thnx for ur attention...
but my problem is not solved

anyways thnx dude..for ur time and attention
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
>dude soory i can't create,insert into another table..:(
that is only a TEMP table variable, which exists only during the scope of the script you run (resp inside the function)

Author

Commented:
i know dude its temporary table..but i don't wanna use any table whether it is temp table or other table..
Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Racim BOUDJAKDJIDatabase Architect - Dba - Data Scientist
CERTIFIED EXPERT

Commented:
<<you can use order by but "i have only one column which contains name" then how can i order by name ?>>
Set of values un-ary relations (single column tables) are by *definition* unordered sets so there is no logical Nth value.  So, as angelII pointed out, you *must* first define a critera by which you consider a value as being the Nth. This critera can *either* be one of the 3 criterias:

> The ASCII position of the Nth *distinct* value of the name
> The ASCII position of the Nth stored value of the name
> The Numeric rank of Nth value based on the order of the output when you run a select * from which is also the order that the compiler makes for you when you run a select * from table

Which one of the above do you want?

Author

Commented:
i want The ASCII position of the Nth *distinct* value of the name
here is my database image..
12.jpg
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
huh?
your original question is:
how to get nth record in sql server

now you say:
i want The ASCII position of the Nth *distinct* value of the name

can you comment on that "change"?
can you comment on my above query?

Author

Commented:
thnx a lot dude."angelll".....this one is working
select name from
(
select name, row_number() over(order by getdate()) as r
  from tablename
) as t
where r = 5

thnx for ur attention...

Author

Commented:
nice programmer..
Racim BOUDJAKDJIDatabase Architect - Dba - Data Scientist
CERTIFIED EXPERT

Commented:
<<huh?
your original question is:
how to get nth record in sql server

now you say:
i want The ASCII position of the Nth *distinct* value of the name>>
Given these are contradicting statements that do not involve the same answers, I doubt the questionner precisely knows what he wants.  
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.