Okay the simple answer is you cannot return a specific row by position in MSSQL Server...you can however do this in Oracle. Select Top X will not return a specific row as you already know, instead it will limit the number of rows returned. As far as I know SQL Server stores its row data in an ordered Heap on the physical disk in pages. Even if you stick a Clustered Index over the top of your column, thereby ordering the physical data on disk, you still cannot access a row number directly. As far as I know Oracle is the only DB that will allow you to do this, but I may be wrong on that one.
Hope this helps,
Phil
Taco Fleur wrote:
Something like this will do you, as I said it will get you going, you need to some experimenting but it can be done.
SELECT TOP 10 * FROM tblOrganisationAddress WHERE fkAddress < 1683 ORDER BY fkAddress DESC
-----Original Message----- From: Ryan Sabir [mailto:[EMAIL PROTECTED] Sent: Wednesday, 26 November 2003 9:01 AM To: CFAussie Mailing List Subject: [cfaussie] RE: Select a single row in SQL
Heya Taco,
TF> TOP 1
TF> TOP 10 PERCENT TF> Use order by to get the bottom
Thats still pretty inefficient tho isn't it? Imagine a 1000 record table that I want record 999 from:
SELECT TOP 999 id, blah1, blah2 FROM blahTable ORDER BY id DESC
I'm still retrieving 998 records that I don't need.
Maybe what I'm thinking of just can't be done? Say it isn't so!
Also, thanks for the note Gary, I am actually ordering the items by id. Its not for paging... I've got a table of datasets for display on a page, and I want to show a different dataset everytime the user views that page.
bye
-----------------------
Ryan Sabir
Newgency Pty Ltd
2a Broughton St
Paddington 2021
Sydney, Australia
Ph (02) 9331 2133
Fax (02) 9331 5199
Mobile: 0411 512 454
http://www.newgency.com/index.cfm?referer=rysig
--- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED]
MXDU2004 + Macromedia DevCon AsiaPac + Sydney, Australia http://www.mxdu.com/ + 24-25 February, 2004
--- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED]
MXDU2004 + Macromedia DevCon AsiaPac + Sydney, Australia http://www.mxdu.com/ + 24-25 February, 2004
