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...
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
Robert Thorne covers electric vehicle innovations, autonomous driving systems, global mobility trends, and automotive engineering developments.
