Solved

how to get nth record in sql server

Posted on 2009-07-09
37
528 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 15
  • 12
  • 6
  • +2
37 Comments
 
LVL 143

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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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 143

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 143

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 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24812591
can you please explain why you need "without order" ?
0
 

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 143

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 143

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 143

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 143

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 143

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 143

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 143

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 143

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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
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.

705 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