PostgreSQL: top n entries per item in same table

Question

| uId |   title   |  amount  |  makers  |  widgets  |
   1     richard      998       xcorp     sprocket
   2     swiss        995       ycorp     framitz
   3     ricky         90       zcorp     flobber
   4     ricky2       798       xcorp     framitz
   1     lilrick      390       xcorp     sprocket
   1     brie         200       mcorp     gullywok
   1     richard      190       rcorp     flumitz
   1     brie         490       bcorp     sprocket

etc...

I am trying to retrieve only 3 records per makers, the top 3 amounts and the widgets they produced

Here's is what I have:

SELECT amount, makers FROM (SELECT amount, makers, (SELECT count(*) FROM  entry  as t2
WHERE t2.amount = t1.amount and t2.makers >= t1.makers) AS RowNum
FROM entry as t1
) t3
WHERE t3.RowNum<4 order by amount;

Is this returning what I actually need? Is there a better way to go about this? Most of the ways I have seen to do this kind of thing are doing joins etc on disparate tables, all the info I need is on one table.

Expected Output:

| uId |   title   |  amounts  |  makers  |  widgets  |
  1      richard      998        xcorp     sprocket
  41     swiss        995        xcorp     widget
  989    richard      989        xcorp     sprocket
  22     swiss        995        ycorp     framitz
  92     swiss        990        ycorp     widget
  456    swiss        895        ycorp     flobber
  344    ricky        490        zcorp     flobber
  32     tricky       480        zcorp     flobber
  13     ricky        470        zcorp     flobber

etc...

The order of the makers doesn't matter so much as getting the top 3 amounts for each makers, and the widgets they provided. The number of makers is set, there will always be x makers

Given the example data in your question, can you show the expected outout as a table? — A.H., Sep 30 '11 at 17:29
That expected output doesn't seem to make any sense given your example data. Also, if you want help you should probably go back and accept past answers to your questions. It's both polite to acknowledge those who have helped you and also makes this website more useful when others are trying to find answers to questions that you've already asked. — Tom H, Sep 30 '11 at 17:51
from 1 million records: I need the 3 'title' with the highest 'amount' and the 'widget' they produced, grouped by 'maker' — roberthuttinger, Sep 30 '11 at 19:19

score 22 · Accepted Answer · answered Sep 30 '11 at 17:51

22

SELECT *
FROM (
   SELECT uid,
          title, 
          amount, 
          maker, 
          widgets,
          rank() over (partition by maker order by amount desc) as rank
   FROM entry  
) t
WHERE rank <= 3

answered Sep 30 '11 at 17:51

a_horse_with_no_name

497,550
91
775
843

7

`rank()` might have gaps and duplicates and therefore the filter `rank <= 3` might return more or less than 3 rows per group. `row_number()` might be more appropriate. – A.H. Sep 30 '11 at 18:52
1

You are right. Another solution would be `dense_rank()` which does not produce gaps. – a_horse_with_no_name Sep 30 '11 at 18:53
im going to look these up and test for results, ill get back thx! – roberthuttinger Sep 30 '11 at 20:01
great solution, and simple! thanks so much! I had never used partition or rank before! – roberthuttinger Sep 30 '11 at 20:40
But what if `amount` will be identical for several columns? `rank()` for these columns will return same value and result set could be larger than 3 rows. `dense_rank()` will fail much more, than usual `rank()` (`rank()` will fail if we have more than 3 rows with top result in field `desc`) – wbars Mar 15 '16 at 02:43
1

@wannabe: then use `row_number()` – a_horse_with_no_name Mar 15 '16 at 06:40
@a_horse_with_no_name can you please edit the answer to have row_number instead of rank? – Damir Ciganović-Janković Jan 11 '21 at 09:00

PostgreSQL: top n entries per item in same table

1 Answers1

Linked

Related