CODEWITHSUNDEEP
sqlpostgresqlwindow-functions
Anurag Chaudhury
Anurag Chaudhury

Reputation: 119

Get all rows with one of the top 2 values in a column

I have a table with multiple entries and I have ordered it according to a sales criterion. So, if the entries are like:

Item  Sales
a     10
b     10
c     9
d     8
e     8
f     7

I want to extract the items with the highest and second highest number of sales. As such, I would want to extract a, b and c.
Is there any function in PostgreSQL that can help with this?

Upvotes: 1

Views: 221

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657122

To include all rows with one of the top two sales values, you could use the dense_rank() window function:

WITH x AS (
   SELECT *
         ,dense_rank() OVER (ORDER BY sales DESC) AS rnk
   FROM   tbl
   )
SELECT item, sales
FROM   x
WHERE  rnk < 3;

You need PostgreSQL 8.4 or later for that.

For older versions, you could:

SELECT *
FROM   tbl
JOIN   (
    SELECT sales
    FROM   tbl
    GROUP  BY 1
    ORDER  BY 1 DESC
    LIMIT  2
    ) t1 USING (sales)

Upvotes: 2

mechanical_meat
mechanical_meat

Reputation: 169374

Use ORDER BY and LIMIT:

  SELECT Item, Sales
    FROM mytable 
ORDER BY Sales DESC 
   LIMIT 2;

Results in:

item    sales
----    -----
a       10
b       9

SQL Fiddle

Upvotes: 1

Related Questions