0

i am using sql server 2005.I have a table containing more than 2 lakh records.i want to display the last 10 records in descending order. i used this query to get the record

select * from course as 
where courseid not in (select top ((select count(*) from course) - 3 ) courseid  
    from course  ) order by courseid desc

but my dba asked to sort the records without using order by clause...is that possible? i am new to the sql server2005. How can i do that?

John Saunders
  • 160,644
  • 26
  • 247
  • 397
prem
  • 1
  • 1
  • 1
  • You'd be surprised how many people don't recall what "lakh" means. Please translate into English. – John Saunders Nov 05 '11 at 05:15
  • check this [answer][1]. [1]: http://stackoverflow.com/questions/311054/how-do-i-select-last-5-rows-in-a-table-without-sorting – franklins Nov 05 '11 at 05:33
  • 2 Lakh (2,00,000) = > 200, 000 (Two hundred thousand.) – franklins Nov 05 '11 at 05:35
  • 1
    why is your dba asking you to not use order by clause? it is like asking someone to steer a vehicle without using a steering wheel. –  Nov 05 '11 at 05:48

2 Answers2

2

It is not possible to reliably get sorted results without explicitly using ORDER BY.

Sources:

The Beatles versus the Stones

Without ORDER BY, there is no default sort order

Gibron
  • 1,350
  • 1
  • 9
  • 28
  • Thanks; fixed "The Beatles versus the Stones" link. That is the better/simpler example since it can be repeated by anyone. – Gibron Nov 05 '11 at 17:16
1

To get the last 10 records in descending order just use

SELECT TOP 10 *
FROM   course
ORDER  BY courseid DESC

Perhaps you misunderstood your DBA's complaint? Likely they were complaining because your current query

  1. Does a full table scan of 200,000 rows to get the count
  2. Then does another scan to get the top 199,997 ids
  3. And uses that in an anti semi join against another full scan of the 200,000 rows

Thus meaning your query is processing a multiple of 60,000 times more rows than seems necessary.

Plan

Martin Smith
  • 438,706
  • 87
  • 741
  • 845