How to get top value in Postgres when ties?

Question

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

name	number_of_cookies
matt	32
Greg	77
vance	21
chen	20
louise	77

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 ?

Answer 1

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;

Answer 2

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