Solved

how to get nth record in sql server

Posted on 2009-07-09
37
523 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
0
Comment
Question by:mahajan344
  • 15
  • 12
  • 6
  • +2
37 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24812285
what version of sql?
what is the "order" you want to apply to determined the "nth" record?
0
 

Author Comment

by:mahajan344
ID: 24812293
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
 
LVL 6

Expert Comment

by:divyeshhdoshi
ID: 24812324
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24812337
wont this work?

select * from table name where id between 2 and 3
0
 
LVL 6

Expert Comment

by:divyeshhdoshi
ID: 24812358
pls. use rank over.
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24812365
or you may use row_number or rank function if you have specific condition for selection.
0
 

Author Comment

by:mahajan344
ID: 24812373
dude how to use rank over function please mention...its very urgent..
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24812393
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
 

Author Comment

by:mahajan344
ID: 24812415
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
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24812428
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
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24812432
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24812434
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
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24812453
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
 

Author Comment

by:mahajan344
ID: 24812485
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
 

Author Comment

by:mahajan344
ID: 24812497
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24812551
>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
 

Author Comment

by:mahajan344
ID: 24812568
its giving error..
Windowed functions do not support integer indices as ORDER BY clause expressions
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24812591
can you please explain why you need "without order" ?
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:mahajan344
ID: 24812610
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24812676
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
 

Author Comment

by:mahajan344
ID: 24812712
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
 

Author Comment

by:mahajan344
ID: 24812788
please tell me is it possible ???
using cursors,trigger,anything but don't use..
insert,create, or any table..
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24812992
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
 

Author Comment

by:mahajan344
ID: 24813016
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24813045
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24813064
>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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24813074
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
 

Author Comment

by:mahajan344
ID: 24813130
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24813154
>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
 

Author Comment

by:mahajan344
ID: 24813177
i know dude its temporary table..but i don't wanna use any table whether it is temp table or other table..
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 150 total points
ID: 24813226
>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
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 24813229
<<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
 

Author Comment

by:mahajan344
ID: 24813353
i want The ASCII position of the Nth *distinct* value of the name
here is my database image..
12.jpg
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24813390
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
 

Author Comment

by:mahajan344
ID: 24813432
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
 

Author Closing Comment

by:mahajan344
ID: 31601514
nice programmer..
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 24813485
<<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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now