CODEWITHSUNDEEP
sqlpostgresql
1252748
1252748

Reputation: 15369

select 3 values for each id based on top values in a count column

How can I select across multiple rows based on a particular column value. For example I have a structure like this

+--+----+-----+
|id|data|count|
+--+----+-----+
|1 |text|30   |
+--+----+-----+
|1 |text|1    |
+--+----+-----+
|1 |text|25   |
+--+----+-----+
|1 |text|12   |
+--+----+-----+
|1 |text|50   |
+--+----+-----+
|2 |text|5    |
+--+----+-----+
|2 |text|2    |
+--+----+-----+
|2 |text|100  |
+--+----+-----+
|2 |text|50   |
+--+----+-----+
|2 |text|1000 |
+--+----+-----+
|3 |text|2    |
+--+----+-----+
|3 |text|4    |
+--+----+-----+
|3 |text|6    |
+--+----+-----+
|3 |text|8    |
+--+----+-----+

And I want to select three of every ID--the "top" 3 based on the highest values in the count column so that I'll end up with:

+--+----+-----+
|id|data|count|
+--+----+-----+
|1 |text|30   |
+--+----+-----+
|1 |text|25   |
+--+----+-----+
|1 |text|50   |
+--+----+-----+
|2 |text|100  |
+--+----+-----+
|2 |text|50   |
+--+----+-----+
|2 |text|1000 |
+--+----+-----+
|3 |text|4    |
+--+----+-----+
|3 |text|6    |
+--+----+-----+
|3 |text|8    |
+--+----+-----+

I am essentially stuck on the first WHERE clause: I don't want the rows where count is above a particular value as that could return more than 3 results. I can do a limit 3 and sort by count, but that will only work for one id. How can I do this for every distinct id in my table?

Upvotes: 1

Views: 1143

Answers (3)

Taryn
Taryn

Reputation: 247820

You should be able to implement a windowing function like row_number() to get the top 3 for each id:

select id, data, "count"
from 
(
  select id, data, "count",
    row_number() over(partition by id order by "count" desc) seq
  from yourtable
) d
where seq <= 3
order by id, "count";

See SQL Fiddle with Demo

Upvotes: 3

Maheswaran Ravisankar
Maheswaran Ravisankar

Reputation: 17920

SELECT "id",data,"count"
FROM  
  (SELECT "id",data,"count" rank() OVER (partition by "id" ORDER BY "count" DESC) as rn 
  FROM 
  your_table) t
WHERE  rn <= 3
ORDER BY "id","count" desc

Upvotes: 1

Mudassir Hasan
Mudassir Hasan

Reputation: 28771

You can use PARTITION BY and RANK() function . See here

Upvotes: 1

Related Questions