CODEWITHSUNDEEP
sqlsql-serversql-server-2005
barsan
barsan

Reputation: 2491

How to Order only first 20 records in a resultset using SQL?

My requirement is to get the List of Diagnosis based on the most used Diagnosis. So, to achieve that I have added one Column named DiagnosisCounter in the tblDiagnosisMst Table of the database which increases by 1 for each Diagnosis the each time user selects it. So, my query is like below:

  select DiagnosisID,DiagnosisCode,Name from tblDiagnosisMst 
  where GroupName = 'Common' and RecStatus = 'A' order by DiagnosisCounter desc, 
  Name asc

So, this query is helping me to get the list of Diagnosis but in descending order for Diagnosis and then alphabetically for Diagnosis Name. But now my client wants to show only 20 most used Diagnosis name at the top and then all the names should appear in alphabetical order. But unfortunately I am stuck in this point. It would be so appreciative if I get your helpful advice for this problem.

Upvotes: 0

Views: 235

Answers (2)

Ajay Kumar
Ajay Kumar

Reputation: 175

Try this

SELECT TOP 20
 * FROM tblDiagnosisMst ORDER BY DiagnosisCounter;

Upvotes: 0

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239754

This should do the trick:

;With Ordered as (
  select DiagnosisID,DiagnosisCode,Name,
    ROW_NUMBER() OVER (ORDER BY DiagnosisCounter desc) as rn
  from tblDiagnosisMst 
  where GroupName = 'Common' and RecStatus = 'A'
)
select * from Ordered
order by CASE WHEN rn <= 20 THEN rn ELSE 21 END, 
  Name asc

We use ROW_NUMBER to assign the numbers 1-x to each of the rows, based on the diagnosiscounter. We then use that value for the first ORDER BY condition if it's in 1-20, and all other rows sort equally in position 21. The second condition is then used as a tie-breaker to sort those remaining row by name.

Upvotes: 6

Related Questions