CODEWITHSUNDEEP
sql-servermaxrepeat
maliks
maliks

Reputation: 1112

How to select top 3 maximum values from SQL Server column

I have values in SQL Server derived column sorted as descending i.e.

id   Count    OR     id   Count    OR     id   Count
1    4               1    5               1    11
2    4               2    2               2    1
3    4               3    1               3    1
4    4               4    1               4    1
5    4               5    1               5    1

Now I want to select top 3 maximum values. How can I select so that query returns consistent result every time.
For example if values of Count are same which id's should be returned as top 3 maximums, similarly if 3rd value is matched with other values and if 2nd value is matched with other values then which id's should be returned by the query. And the result should be consistent every time I execute the query.

Upvotes: 2

Views: 48784

Answers (1)

BeanFrog
BeanFrog

Reputation: 2315

The with ties argument of the top function will return all the of the rows which match the top values:

select top (3) with ties id, count from table1
order by count desc

Alternatively, if you wanted to return 3 values only, but make sure they are always the same 3 values, then you will need to use something else as a tie-breaker. In this case, it looks like your id column could be unique.

select top (3) id, count from table1
order by count desc, id

Upvotes: 2

Related Questions