SQL order by multiple columns-CodePudding

How can I select data from my SQL table which look like this:

| Title | Date      | Active |
| A     | 2022-02-01| 1|
| B     | 2022-01-01| 1|
| C     | 2022-04-01| 1|
| A     | 2021-02-01| 0|
| B     | 2021-03-01| 0|
| C     | 2020-04-01| 0|

So result should look like this:

| Title | Date      | Active |
| B     | 2022-01-01| 1|
| B     | 2021-03-01| 0|
| A     | 2022-02-01| 1|
| A     | 2021-02-01| 0|
| C     | 2022-04-01| 1|
| C     | 2020-04-01| 0|

Order first by date and group by title, and active first. GROUp BY date, title, active gives me order by date only.

CodePudding user response：

If I understand correctly, you want to order by the minimum date per title group ascending, followed by the Active column descending. We can use:

SELECT Title, Date, Active
FROM yourTable
ORDER BY MIN(Date) OVER (PARTITION BY Title), Active DESC;

CodePudding user response：

Assuming this is to track the chaning of active/inactive by date on these objects - Try:

ORDER BY TITLE ASC, DATE DESC

Partition would also work but I don't think it's necessary for this specific example.