SELECT QUERY - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SELECT QUERY

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-12-20 : 07:57:52

Ravindra writes "How to retrieve n th row from a table"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-12-20 : 08:15:35

http://weblogs.sqlteam.com/mladenp/archive/2005/08/01/7421.aspx
point 2

will explain all you need.

Go with the flow & have fun! Else fight the flow

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-20 : 08:41:40

This may be more efficient than other methods

Select min(col) from
(
Select Top N col from yourTable order by col DESC
) T

Madhivanan

Failing to plan is Planning to fail

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2005-12-20 : 10:05:12

More than one way to skin that cat.

If you need it portable, you might do


USE Pubs
SELECT 
 e1.hire_date
FROM 
 employee AS e1
INNER JOIN 
 employee AS e2
ON 
 e1.hire_date <= e2.hire_date
GROUP BY 
 e1.hire_date
HAVING COUNT(DISTINCT e2.hire_date) = 3

If it's stricly for SQL Server, here's another alternative


USE Pubs
DECLARE @dt DATETIME
SELECT TOP 3
 @dt = hire_date
FROM
 employee
ORDER BY
 hire_date DESC
SELECT @dt

--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt? http://www.insidesql.de/blogs

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-20 : 10:11:28

Well Frank
First one is time consuming and second is similar to what I suggested and I always prefer to use that

Madhivanan

Failing to plan is Planning to fail

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2005-12-21 : 04:10:08

I know that the first one is probably the most costly. However, as it is ANSI-SQL almost any even half-smart database should understand it. That's why I wrote "if you need it portable".

--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt? http://www.insidesql.de/blogs

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-12-21 : 11:38:33

sorry, the correct answer was "There is no Nth row. Row numbers have no meaning in SQL SERVER".

Help us help YOU!
Read this blog entry for more details: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

*need more coffee*
SELECT * FROM Users WHERE CLUE > 0
(0 row(s) affected)

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2005-12-21 : 11:59:25

My opinion is:
nth row is defined when ordered by primary key.
Any ideas ?

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2005-12-21 : 15:02:03

quote:

sorry, the correct answer was "There is no Nth row. Row numbers have no meaning in SQL SERVER".

Courtesy of Joe Celko...

quote:

Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files. Since you are not thinking in
the right terms, you will never "get it" until you do.

...oh my....

--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt? http://www.insidesql.de/blogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources