How to select top 3 maximum values from SQL Server column

Question

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.

BeanFrog · Accepted Answer · 2015-12-03 11:46:05Z

2

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

answered Dec 3, 2015 at 11:46

BeanFrog

2,32514 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to select top 3 maximum values from SQL Server column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related