CODEWITHSUNDEEP
sql-server
prem
prem

Reputation: 1

how can i get the last records in descending order without using order by clause in sql server2005?

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?

Upvotes: 0

Views: 4075

Answers (2)

Gibron
Gibron

Reputation: 1369

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

Upvotes: 2

Martin Smith
Martin Smith

Reputation: 453426

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

Upvotes: 1

Related Questions