Home > OS >  Set new field priority in SELECT SQL
Set new field priority in SELECT SQL

Time:05-11

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

  • Related