Sort aggregated query results by two methods simultaneously

Question

I need to sort a query's results by two methods at the same time. I want the first 3 records (returned) to be based on their prevalence in another table And then I want the rest of the results sorted alphabetically.

Assuming I have 6 records in a query result set....

empl_Type	count
A	10
B	5
C	2
D	1
E	1
F	20

then results should be F,A,B,C,D,E

• F,A,B sorted by their count in another table
• C,D,E (remainder of rows) sorted alphabetically

I have the first part working in the following contrived example:

SELECT 
    et.id,
    et.employeetype_description,
    count(e.employeetypeid) as thesortorder
FROM 
    employeetype et
LEFT JOIN
    employee e ON e.employeetypeid = et.id
GROUP BY
    et.id,
    et.employeetype_description
ORDER BY thesortorder DESC

And this is where (hopefully you come in...) How do I meet the rest of the requirements? Thanks

Answer 1

Window functions are allowed in order by, even if you're aggregating. _{demo at db<>fiddle}

ORDER BY/*1*/least(4,row_number()over(order by count(e.employeetypeid)desc)),
        /*2*/id

id	employeetype_description	thesortorder
F	F_description	20
A	A_description	10
B	B_description	5
C	C_description	1
D	D_description	3
E	E_description	2

• row_number()over(order by count(..)) scores rows by prevalence in another table
• least(4,row_number(..)) makes sure the top 3 get positions 1,2,3, the rest fights for 4
• among 4's, alphabetical order of id decides as the 2nd order by expression

Quoting the doc:

Window functions are permitted only in the SELECT list and the ORDER BY clause of the query. They are forbidden elsewhere, such as in GROUP BY, HAVING and WHERE clauses. This is because they logically execute after the processing of those clauses. Also, window functions execute after non-window aggregate functions. This means it is valid to include an aggregate function call in the arguments of a window function, but not vice versa.

---

You can clean things up a bit with a named window clause (also in MySQL, SQLite, Trino, SQL Server 2022 (16+):

SELECT et.id,
       et.employeetype_description,
       count(e.employeetypeid) as thesortorder
FROM employeetype et
LEFT JOIN employee e ON e.employeetypeid = et.id
GROUP BY et.id,
         et.employeetype_description
WINDOW w1 AS (order by count(e.employeetypeid) desc)
ORDER BY /*1*/ least((row_number() over w1), 4),
         /*2*/ id

---

In this method, the additional tie-breaking @ThorstenKettner's comment asks to specify, simply goes into the window spec:

• To use count(*) for placing more than 3 rows on top in case their counts draw:

ORDER BY least(4,dense_rank()over(order by count(e.employeetypeid)desc)),id

• To use alphabetical sorting to break ties on top 3 positions:

ORDER BY least(4,row_number()over(order by count(e.employeetypeid)desc, id)),id