D
D
Dmitry2022-04-15 19:40:00
SQL
Dmitry, 2022-04-15 19:40:00

How to correctly form a window function into a query?

There is data:

cat_id | ad_id | count
60     | 1     | 22
60     | 99    | 22
60     | 3     | 22
42     | 5     | 14
42     | 4     | 14
11     | 12    | 6
11     | 19    | 6
11     | 2     | 6


How to correctly add a window function to get:
cat_id | ad_id | count | row_num
60     | 1     | 22    | 1
60     | 99    | 22    | 1
60     | 3     | 14    | 1
42     | 5     | 14    | 2
42     | 4     | 14    | 2
11     | 12    | 14   | 3
11     | 19    | 6     | 3
11     | 2     | 6     | 3

This is necessary in order to put a restriction on this new row_num field further in the query, for example row_num < 3.

A simple ROW_NUMBER will not work, since it creates an ordinal series inside each cat_id set, and I need it to assign a serial number to each set when sorting by count.
SELECT *, ROW_NUMBER() OVER(
        PARTITION BY cat_id
        ORDER BY count desc
    ) AS row_num
FROM tabl

Answer the question

In order to leave comments, you need to log in

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question