Search code examples
sqlsqlitesql-order-bywindow-functions

How to write a custom sort using SQLite

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.

Solution

  • 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.