how to get nth record in sql server

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
mahajan344Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
what version of sql?
what is the "order" you want to apply to determined the "nth" record?
0
mahajan344Author 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..
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

RiteshShahCommented:
wont this work?

select * from table name where id between 2 and 3
0
divyeshhdoshiCommented:
pls. use rank over.
0
RiteshShahCommented:
or you may use row_number or rank function if you have specific condition for selection.
0
mahajan344Author Commented:
dude how to use rank over function please mention...its very urgent..
0
RiteshShahCommented:
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
0
mahajan344Author 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..
0
RiteshShahCommented:
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

0
RiteshShahCommented:
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
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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

0
RiteshShahCommented:
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
0
mahajan344Author 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
0
mahajan344Author 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..
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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

0
mahajan344Author Commented:
its giving error..
Windowed functions do not support integer indices as ORDER BY clause expressions
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
can you please explain why you need "without order" ?
0
mahajan344Author 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......
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
0
mahajan344Author 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..

0
mahajan344Author Commented:
please tell me is it possible ???
using cursors,trigger,anything but don't use..
insert,create, or any table..
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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.
0
mahajan344Author 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 ??
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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

0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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

0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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

0
mahajan344Author 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
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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)
0
mahajan344Author Commented:
i know dude its temporary table..but i don't wanna use any table whether it is temp table or other table..
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>i know dude its temporary table..but i don't wanna use any table whether it is temp table or other table..
WHY, dude?

is this some school question, or what?

I played around, and it seems this should work:
select name from
(
select name, row_number() over(order by getdate()) as r 
  from tablename
) as t
where r = 5

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<<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?
0
mahajan344Author Commented:
i want The ASCII position of the Nth *distinct* value of the name
here is my database image..
12.jpg
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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?
0
mahajan344Author 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...

0
mahajan344Author Commented:
nice programmer..
0
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<<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.  
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.