How can I sort data into 3 alphabetical subgroups with SQL?

I'm displaying student data for teachers at my school using SQL, and for usability reasons it needs to be displayed with very unique sorting, with 3 subsets of the alphabet dividing the dataset, then normal sorting within that.

It should spit out something like this:

Last names A-F, sorted by period, then by last name

Last names G-O, sorted by period, then by last name

Last names P-Z, sorted by period, then by last name

It's currently sorted by period and last name fairly simply, and I can use three different queries to split the data as such, but I don't know how to do it all in one.

SELECT * FROM Student ORDER BY per, last;

3 Answers

Are you looking for a conditional expression in the order by?

order by (case when last < 'G' then 1 when last < 'P' then 2 else 3 end), per, last

You may want to try something like this:

SELECT CASE WHEN LEFT(UPPER(last), 1) BETWEEN 'A' AND 'F' THEN 1 WHEN LEFT(UPPER(last), 1) BETWEEN 'G' AND 'O' THEN 2 WHEN LEFT(UPPER(last), 1) BETWEEN 'P' AND 'Z' THEN 3 ELSE 4 END AS LETTER_GROUP, per, last
FROM Student
ORDER BY 1,2,3;

You can't really break the result set up into groups/partitions. You can group the results set with a group by clause, but that summarizes down to a single row per group. There's also over/partition by, but that's more analytic. You'll want to do something like this:

select group_1 = case when upper(substring(t.surname,1,1)) between 'A' and 'F' then '1:A-F' when upper(substring(t.surname,1,1)) between 'G' and 'O' then '2:G-O' when upper(substring(t.surname,1,1)) between 'P' and 'Z' then '3:P-Z' else '4:Other' end, *
from my_table t -- SORT SEQUENCE:
order by 1, -- 1. Ordinal Column 1 (grouping by letter of last name) t.period, -- 2. Period t.surname -- 3. Surname

You don't say what flavor of SQL you're using. Depending on what dialect your DBMS speaks, you have to restate the 1st column

group_1 = case ... end

to be

case ... end as group_1