I have a table of bills with the following structure:

id | store_name | sum | payment_date
1  | Amazon     | 10  | 11.05.2022
2  | Amazon     | 20  | 11.05.2022
3  | Ebay       | 15  | 11.05.2022
4  | AppleStore | 13  | 11.05.2022
5  | Google Play| 6   | 11.05.2022

What I need is to select all data from table and set additional field "Priority" based on a sum of bill. First 2 rows get priority 1, next 2 rows get priority 2, others get 0:

id | store_name | sum | payment_date | priority
2  | Amazon     | 20  | 11.05.2022   | 1
3  | Ebay       | 15  | 11.05.2022   | 1
4  | AppleStore | 13  | 11.05.2022   | 2
1  | Amazon     | 10  | 11.05.2022   | 2
5  | Google Play| 6   | 11.05.2022   | 0

In addition table contains data about bills from various days (field payment_date) and this priority should be set based on data inside each single day.

CodePudding user response：

Order the rows for each day and then assign priority based on the row number:

SELECT t.*,
       CASE ROW_NUMBER()
              OVER (PARTITION BY TRUNC(payment_date) ORDER BY sum DESC)
       WHEN 1 THEN 1
       WHEN 2 THEN 1
       WHEN 3 THEN 2
       WHEN 4 THEN 2
       ELSE 0
       END AS priority
FROM   table_name t

Which, for the sample data:

CREATE TABLE table_name (id, store_name, sum, payment_date) AS
SELECT 1, 'Amazon',      10, DATE '2022-05-11' FROM DUAL UNION ALL
SELECT 2, 'Amazon',      20, DATE '2022-05-11' FROM DUAL UNION ALL
SELECT 3, 'Ebay',        15, DATE '2022-05-11' FROM DUAL UNION ALL
SELECT 4, 'Apple Store', 13, DATE '2022-05-11' FROM DUAL UNION ALL
SELECT 5, 'Google Play',  6, DATE '2022-05-11' FROM DUAL;

Outputs:

ID	STORE_NAME	SUM	PAYMENT_DATE	PRIORITY
2	Amazon	20	2022-05-11 00:00:00	1
3	Ebay	15	2022-05-11 00:00:00	1
4	Apple Store	13	2022-05-11 00:00:00	2
1	Amazon	10	2022-05-11 00:00:00	2
5	Google Play	6	2022-05-11 00:00:00	0

db<>fiddle here