Everywhere people select top value using ORDER BY and LIMIT. But how to deal a scenario where the last element has duplicate entries.

Consider a 5 row table

Now I want the person with most number of cookies. If you query like this

select * from table ORDER BY number_of_cookies DESC LIMIT 1;

This will get only one person either Louise or greg. But here the answer is both people have most number of cookies. How to deal with these kind of scenario in Postgres when using LIMIT?

Also if I extend this question further and if I want to list top 10 values, and a situation like this(ties) exist then how I can deal with it ?

CodePudding user response：

You could use the RANK analytic function:

WITH cte AS (
    SELECT *, RANK() OVER (ORDER BY number_of_cookies DESC) rnk
    FROM yourTable
)

SELECT name, number_of_cookies
FROM cte
WHERE rnk = 1;

CodePudding user response：

you can try something like that:

select * from table 
where number_of_cookies = (select max(number_of_cookies) from table)

this will return all names with maximal number