Partition by with order by Date and Active-CodePudding

I have a table:

| Title    | Expire_date | Active |
| -------- | --------    |--------|
| Apple    | 01-01-2023  |1       |
| Banana   | 01-01-2022  |1       |
| Orange   | 01-06-2021  |1       |
| Banana   | 01-01-2021  |0       |
| Apple    | 01-01-2021  |0       |
| Apple    | 01-01-2020  |0       |
| ...      | ...         |...     |
| Fruit_999| 01-01-2020  |1       |

And want to get result form selelect what looks like this:

Title	Expire_date	Active
Fruit_999	01-01-2020	1
Orange	01-06-2021	1
Banana	01-01-2022	1
Banana	01-01-2021	0
Apple	01-01-2023	1
Apple	01-01-2021	0
Apple	01-01-2020	0
...	...	...

So, first it should order by Active, then by Expire_dateand group results by Title.

1)doesn't work here, it won't group by title.

ORDER BY expire_date, active

2)will put Apple on the top, although Orange and Fruit_999 dates are less, thats wrong

SELECT * FROM `table` ORDER BY MIN(`expire_date`) OVER (PARTITION BY `title`), `active` DESC;

CodePudding user response：

You should using analytic functions (oracle) or window functions (MSSQL)... to achieve your expected result.

Because you didn't tag your RDBMS, this is running query for Oracle. Convert it to suit your RDBMS.

WITH table_name AS 
(
    SELECT  'Apple' AS Title, TO_DATE('01-01-2023', 'DD-MM-YYYY') AS expire_date,  1 AS active FROM dual UNION ALL      
    SELECT  'Banana' AS Title, TO_DATE('01-01-2022', 'DD-MM-YYYY') AS expire_date,  1 AS active FROM dual UNION ALL      
    SELECT  'Orange' AS Title, TO_DATE('01-06-2021', 'DD-MM-YYYY') AS expire_date,  1 AS active FROM dual UNION ALL      
    SELECT  'Banana' AS Title, TO_DATE('01-01-2021', 'DD-MM-YYYY') AS expire_date,  0 AS active FROM dual UNION ALL      
    SELECT  'Apple' AS Title, TO_DATE('01-01-2021', 'DD-MM-YYYY') AS expire_date,  0 AS active FROM dual UNION ALL      
    SELECT  'Apple' AS Title, TO_DATE('01-01-2020', 'DD-MM-YYYY') AS expire_date,  0 AS active FROM dual UNION ALL      
    SELECT  'Fruit_999' AS Title, TO_DATE('01-01-2020', 'DD-MM-YYYY') AS expire_date,  1 AS active FROM dual 
),
title_active AS
(
    SELECT Title, 
        MIN(CASE WHEN active = 1 THEN expire_date ELSE TO_DATE('01-01-3000', 'DD-MM-YYYY') END) AS min_active_expire_date, 
        MAX(active) AS max_active
    FROM table_name
    GROUP BY title
),
title_ordered AS 
(
    SELECT ROW_NUMBER() OVER (ORDER BY max_active DESC, min_active_expire_date) AS rn,
        t.*
    FROM title_active t
)
SELECT t.title, t.expire_date, t.active--*
FROM table_name t
INNER JOIN title_ordered ti
ON t.title = ti.title
ORDER BY ti.rn, t.active DESC, t.expire_date;

Tested with sqlfiddle

P/S: this query should work for about 978 years. After that, if you keep running, this query could return wrong result...

CodePudding user response：

Try the following:

select * from table_name
order by max(Expire_date) over (partition by Title),
         Title, Active desc, Expire_date

This will order the rows according to the latest Expire_date for each title, the title with the oldest date appears first. If there are two titles with the same max(Expire_date) then sort by title to keep titles consecutive (in groups). And for rows within the same title (same max date) order by Active descending to show active rows first for each title.

See demo.

CodePudding user response：

Try this. The query will order the rows by the first column, then, without destroying the first rule, by the second column. Then, in the same way, without violating the existing rules, according to the third, etc.

SELECT * FROM `events` ORDER BY Title DESC, Expire_date ASC