SQL query - how to limit number of rows per a specific column

Question

I need to sample a group of products. Products belong to some Form. Form can be mapped to some catalogs.

For example, form=1523 is mapped to catalog =5 and catalog=8

I need to run on a specific form and to bring maximum 750 products from each catalog.

I thought of using limit (RowNum<=750*number of catalogs) but I don't understand how I can limit per catalog and not the total query result.

Also, I don't have a previous knowledge of how many catalogs there are in a specific form.

For example, lets say i want only 2 rows per each catalog. My sample is:

Product  Form   Catalog
-----------------------
1        50     5
2        50     6
3        50     6
4        50     6

How can I get a result of product 1 and 2 random products from catalog 6?

Your description is a little lacking, but can you use LIMIT ??somehow? — domdomcodecode
– domdomcodecode, Commented Mar 4, 2014 at 13:43
I will try to explain better. What you are saying (using limit) is exactly what i am asking (PLSQL is using RowNum) - how can i limit per catalog and not per whole answer — Alex
– Alex, Commented Mar 4, 2014 at 13:47

Tony Andrews · Accepted Answer · 2014-03-04 13:57:19Z

4

You can use the analytic function row_number():

select product, form, catalog from
( select product, form, catalog,
         row_number() over (partition by catalog order by product, form) as rn
  from mytable
)
where rn <= 750

The partition by catalog clause gets row numbers within catalog. You need to put the order by clause in there too as I have, but you may want to order differently, that's up to you.

answered Mar 4, 2014 at 13:57

Tony Andrews

133k23 gold badges230 silver badges266 bronze badges

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

Collectives™ on Stack Overflow

SQL query - how to limit number of rows per a specific column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related