Querying for a particular row - 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)

Querying for a particular row

Author

Topic

gv_pradeep
Starting Member

19 Posts

Posted - 2008-07-24 : 07:57:00

Hi,
In SQL Server 2000, is there any way to retrieve the nth row directly.?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-24 : 07:59:11

Yes.
If you have an identity column with no gaps, yes.

Otherwise, you can use a correlated subquery but performance will suffer.

E 12°55'05.25"
N 56°04'39.16"

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-07-24 : 07:59:48

nth row based on what order?

Em

gv_pradeep
Starting Member

19 Posts

Posted - 2008-07-24 : 08:03:27

Peso, I don't have an identity column.
Em, I have a list of name and their salaries. Need to select second top and second least salaried person.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-24 : 08:15:37

How does it feel to have other people do your homework?

-- Second top 
SELECT		TOP 1 WITH TIES
		Name,
		Salary
FROM		(
			SELECT		TOP 2 WITH TIES
					Name,
					Salary
			FROM		Employees
			ORDER BY	Salary DESC
		) AS d
ORDER BY	Salary

-- Second least
SELECT		TOP 1 WITH TIES
		Name,
		Salary
FROM		(
			SELECT		TOP 2 WITH TIES
					Name,
					Salary
			FROM		Employees
			ORDER BY	Salary
		) AS d
ORDER BY	Salary DESC

E 12°55'05.25"
N 56°04'39.16"

gv_pradeep
Starting Member

19 Posts

Posted - 2008-07-24 : 08:51:26

Peso, Thanks for the efforts. I've figured this out already. In SQL 2005, there is a direct way of getting this in SQL 2005.

SELECT * FROM
(
SELECT Row_Number() OVER (ORDER BY last_name, first_name) as rowid,user_id,last_name,first_name FROM user )
as a where rowid = 28

So, I was wondering whether there is any such function in SQL 2000.
The method which you have given will work fine. But as the query gets complex and when we need to get say alternate rows from a table, it becomes a huge pain.

gv_pradeep
Starting Member

19 Posts

Posted - 2008-07-24 : 08:57:19

One possible way is creating a temp table with an identity column attached to our table and manipulating it.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-24 : 11:28:13

If you already knew that, why post in SQL Server 2000 forum?

E 12°55'05.25"
N 56°04'39.16"

gv_pradeep
Starting Member

19 Posts

Posted - 2008-07-25 : 01:37:26