SQL - return nth occurrence rows

user_15020580 0
Viewed 35

I'm working with PostgreSQL and I should filter a table to get back how many copies of a book we have from each of the authors in case (if and only if) that was the authors' 3rd release. Here is an example table:

id author_id published number_of_copies title
1 1 2002 10 ...
2 1 2000 1 ...
3 2 2000 1 ...
4 3 2001 1 ...
5 1 2000 1 ...
6 2 2010 10 ...
7 3 2002 1 ...
8 3 2002 10 ...
9 2 2005 1 ...

This should be the result of the query:

author_id title number_of_copies
1 ... 10
2 ... 10
3 ... 10

If all of the three occurrences have been released in the same year then the ID would be the base of the order. Is it possible to solve this problem with a query?

postgresql sql
2 Answers

I think you want row_number():

select t.*
from (select t.*,
             row_number() over (partition by author_id order by published) as seqnum
      from t
     ) t
where seqnum = 3;
user_11440350

you can use dense rank by partitioning on author_id and ordering by author_id,published, id in the over clause

declare @tmp as table(id int, author_id int, published int, number_of_copies int)

insert into @tmp
values
(8, 3,  2002,   10),    
(9, 2,  2005,   1),
(1, 1,  2002,   10),    
(2, 1,  2000,   1), 
(3, 2,  2000,   1), 
(4, 3,  2001,   1), 
(5, 1,  2000,   1), 
(6, 2,  2010,   10),
(7, 3,  2002,   1)


select 
* 
from
(
select
Dense_Rank() over(partition by author_id  order by author_id,published, id) 
author_rank, * 
from @tmp
)x
where author_rank=3
order by author_id, published

output

author_rank id  author_id   published   number_of_copies
3   1   1   2002    10
3   6   2   2010    10
3   8   3   2002    10
user_40011770
Related