How to write a custom sort using SQLite-CodePudding

I have a single table

Create Table Part(Part TEXT, Rev TEXT, DateCode Date, Unique(Part,Rev))

Is it possible to perform a custom sort by DateCode DESC but for the records with same Part should be grouped together for example result:

PART_1, B, 2022-02-14
PART_1, A, 1999-01-11
PART_2, C, 2000-02-24
PART_2, B, 1998-11-12
PART_2, A, 1998-11-10

My instinct tells me it must be done with

ORDER BY CASE WHEN....

But my knowledge is not good enough to continue. Please help me.

CodePudding user response：

You can use MAX() window function in the ORDER BY clause to get the max DateCode of each part and sort by that descending:

SELECT *
FROM Part
ORDER BY MAX(DateCode) OVER (PARTITION BY Part) DESC,
         Part, -- just in case 2 different parts have the same max DateCode
         DateCode DESC;

See the demo.

CodePudding user response：

To me it looks a simple case of sorting it by Part first and Date second

SELECT * FROM Part order by Part,DateCode Desc

Sqlfiddle for SQLlite for this case here I think I am surely missing something ..