Postgresql selecting with limit equal values?

Question

I have one postgresql table where I store some stories from different sites. At this table I got story_id and site_id fields. Where story_id is the primary key and site_id is the id of the site where I got this story from.

I need to make SELECT from this table picking the latest 30 added stories. But I dont want to get more than 2 stories comming from same site...

So if I have something like this:

story_id  | site_id
    1     |    1
    2     |    1
    3     |    2
    4     |    1
    5     |    3

My results must be : story_ids = 1,2,3,5!
4 must be skipped because I have already picked 2 ids with site_id 1.

Please define "latest". Without a date-like field (or sequence number) there is no "latest" — wildplasser
– wildplasser, Commented Jun 27, 2012 at 12:44
latest = ORDER BY story_id DESC I have written just 5 entries, at my table there are few thousands. — Svetoslav
– Svetoslav, Commented Jun 27, 2012 at 12:45

score 2 · Accepted Answer · 2012-06-27 13:06:08Z

2

select story_id,
       site_id 
from (
   select story_id,
          site_id,
          row_number() over (partition by site_id order by story_id desc) as rn
   from the_table
) t
where rn <= 2
order by story_id desc
limit 30

If you want more or less than 2 entries "per group" you have to adjust the value in the outer where clause.

edited Jun 27, 2012 at 13:06

answered Jun 27, 2012 at 12:50

user330315

Sign up to request clarification or add additional context in comments.

2 Comments

Svetoslav Over a year ago

Yes this worked perfect! Thanks, just one little miss at the Partition the order must be set DESC too. Thank you!

user330315 Over a year ago

@Svetlio: I wasn't sure about that. Corrected

Collectives™ on Stack Overflow

Postgresql selecting with limit equal values?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related