 mrstevey
|
 |
| Joined: 17 Jul 2006 |
| Total Posts: 16 |
| |
|
Finding the nth Row
Posted: 26 Feb 2008 10:48 AM |
Hi All,
I have a requirement to get the nth row from a table. i've tried the following sql but the problem is that if there are n-1 records in the table, it will return me that rows as the nth row, which is obviously incorect.
SELECT TOP 1 * FROM (SELECT top 5 * FROM TableA order by ID) [A] ORDER BY ID DESC
eg, if i want the 5th record but there are only 4 records in the table, it will return me the 4th record.
any ideas?
Thanks in advanced
|
|
|
 |
|
|
Re: Finding the nth Row
Posted: 27 Feb 2008 08:21 PM |
How can you expect to get the 5th row from a table with only 4 rows?
What do you expect to be returned if there's no row there? It might help to post a sample table with some rows (create table + insert statements) + expected resultset to clarify what you mean here.. |
|
|
|
|
mrstevey
|
|
| Joined: 17 Jul 2006 |
| Total Posts: 16 |
| |
|
Re: Finding the nth Row
Posted: 28 Feb 2008 09:58 AM |
opps, i forgot to mention that i want it to retun an empty result set if there is nothing in the 5th row.
It's a very simple table,
CREATE TABLE [dbo].[Club] (
[ClubID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Email] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
i want to be able to send out an email once the 150th, 200th...etc club has joined our group.
I was thinking that each time a new club joined, i would have to run this query i'm trying to write to see if they are the 150th..etc club to join.
am i on the right track? |
|
|
|
|
|
Re: Finding the nth Row
Posted: 28 Feb 2008 10:57 AM |
Assuming you can always use the literal value in the ClubID column, you can simply use WHERE for this, eg:
select top 1 *
from Club
where ClubID > 4
order by ClubID
If you need to get the n'th row, where there might be gaps in the ClubID ranges, use row_number() with a CTE, though this technique is less efficient, eg:
with orderedclubs as
(select row_number() over (order by clubid) as rownumber, * from club)
select clubid, name, email
from orderedclubs
where rownumber > 4
|
|
|
|
|
mrstevey
|
|
| Joined: 17 Jul 2006 |
| Total Posts: 16 |
| |
|
Re: Finding the nth Row
Posted: 28 Feb 2008 12:26 PM |
Excellent,
It's just what i was after!
Thanks Greg |
|
|
|
|