FINDING ROW N IN A TABLE - 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

Transact-SQL (2000)

FINDING ROW N IN A TABLE

Author

Topic

SQLISCOOL
Starting Member

5 Posts

Posted - 2005-05-06 : 06:34:36

Supposing I do a:
SELECT * FROM MyTable
and I get dozens of results

How can I select only, for example, row 7.

I now I could do SELECT TOP 7 from MyTable but this will still bring back an extra 6 rows that I don't want. Any ideas?

Thanks

RM
Yak Posting Veteran

65 Posts

Posted - 2005-05-06 : 06:49:39

What is your criteria of identifying row no. In what order are you sorting the records?

SQLISCOOL
Starting Member

5 Posts

Posted - 2005-05-06 : 07:31:02

Thanks for the reply,

I'm not really sorting the results in any way. I'm just doing a 'SELECT * ..' and retrieving whatever comes back

I believe I'm half way there. I want to select whatever happens to be the (for example) 7 th row in the table.

At the moment I am saying:

SELECT TOP 7 FROM MyTable

Now, I only want the last result (i.e the seventh row) in the dataset to show.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-05-06 : 07:56:00

How are you ordering your results? what is the primary key of your table? Never use TOP without an ORDER BY.

- Jeff

EDIT: Added a small insignificant minor detail to correct a small minor logic error in my original statement ...

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-05-06 : 07:58:33

Don't you mean "without an ORDER BY"?

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-05-06 : 08:43:49

Doesn't this go back to the "SQL does not care about row numbers, it uses SET DATA. If you like rows, go back to Excel" argument?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-05-06 : 09:20:17

quote:
Originally posted by robvolk

Don't you mean "without an ORDER BY"?

Uh, yes ....

- Jeff

SQLISCOOL
Starting Member

5 Posts

Posted - 2005-05-06 : 11:00:56

My primary Key is 'RefNo'. The table is ordered by RefNo by default

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-05-06 : 11:18:14

>> The table is ordered by RefNo by default
No it is not !!!

select <column_list> from tbl
where RefNo = (	select top 1 RefNo from
		(select top 7 RefNo from tbl order by RefNo asc) top7
		order by RefNo desc )

rockmoose

SQLISCOOL
Starting Member

5 Posts

Posted - 2005-05-06 : 11:51:32

Excellent,

That done the trick perfectly. I knew it was possible.

Thanks

X002548
Not Just a Number

15586 Posts

Posted - 2005-05-06 : 12:06:07

quote:
Originally posted by jsmith8858

quote:
Originally posted by robvolk

Don't you mean "without an ORDER BY"?

Uh, yes ....

- Jeff

Jeff's having a rough week....

I can't wait to see the bar tab at the beer works...

Brett

8-)

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-05-06 : 12:20:09

Never is pretty strong, I quite often use TOP without an ORDER BY,
when I just want to browse some data from a table.

SQLISCOOL,
There is no inherent order of the rows in a table,
although a user of the database can specify an ORDER BY clause to retrieve the rows in some particular order.

rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources