sql - First n rows per group combination -
i'm trying first n rows each group combination, example
with following input first 2 rows per product-area combination, ordered date:
product area date units 2015/08/07 10 2015/08/08 12 2015/08/09 15 uk 2015/08/07 13 uk 2015/08/10 11 b 2015/08/07 16 b 2015/08/08 17 b 2015/08/09 12 b uk 2015/08/07 10 b uk 2015/08/08 09 b uk 2015/08/09 07
would return
product area date units 2015/08/07 10 2015/08/08 12 uk 2015/08/07 13 uk 2015/08/10 11 b 2015/08/07 16 b 2015/08/08 17 b uk 2015/08/07 10 b uk 2015/08/08 09
i've tried:
with ordered (select product, area, date, units, row_number() on (partition product, area order date asc) date_rank mytable) select product, area, date, units ordered date_rank <= 2
your query returns correct results. thing see missing query order by
final select
:
with ordered ( select product, area, date, units , row_number() on (partition product, area order date asc) date_rank mytable ) select product, area, date, units ordered date_rank <= 2 order product, area desc, date_rank
rextester demo: http://rextester.com/xoms68876
returns:
+---------+------+------------+-------+ | product | area | date | units | +---------+------+------------+-------+ | | | 2015-08-07 | 10 | | | | 2015-08-08 | 12 | | | uk | 2015-08-07 | 13 | | | uk | 2015-08-10 | 11 | | b | | 2015-08-07 | 16 | | b | | 2015-08-08 | 17 | | b | uk | 2015-08-07 | 10 | | b | uk | 2015-08-08 | 9 | +---------+------+------------+-------+
Comments
Post a Comment