SQL order by ASC Second(value) space ASC First(value) in same column in aggregate SQL query

Question

I want to order by a column name that has values like "Value1" Space "Value2" in the same column. Eg.

Column1
Value1 value2
NSPP02 2111
NSPP02 2110
NSPP01 2111

I want to order it by value2 first and then by value1. ASC by value 2 and then ASC by Value 1.

I am using aggregate as follows:

select 
    pdc.semester as Semester,
    count(pdc.CNIC) as TotalRegistrations,
    count(s.studentid) as TotalRegistered,
    count(case when r.Grade is not null then 'Appeared' end) as Appeared,
    count(case when r.Grade='f' then 'fail' end) as Failed,
    count(case when r.grade <>'f' then 'pass' end) as Passed
from
    PDC_PreRegistration pdc 
left join  
    Students s on pdc.Semester = s.CurSemester and s.nic = pdc.CNIC
left join 
    studentresultnet r on s.studentid = r.studentid and s.CurSemester = r.Semester 
left join 
    semester se on se.Semester = r.Semester
where 
    pdc.semester in (select CurSemester from students where batch = 'PD2110') 
group by 
    pdc.Semester
order by  
    semester desc

The result of this query is the following:

Semester	TotalRegistrations	TotalRegistered	Appeared	Failed	Passed
NSPP02 2111	121	81	0	0	0
NSPP02 2110	37	35	24	1	23
NSPP01 2111	76	42	0	0	0

I want the result to be like this:

Semester	TotalRegistrations	TotalRegistered	Appeared	Failed	Passed
NSPP02 2110	37	35	24	1	23
NSPP01 2111	76	42	0	0	0
NSPP02 2111	121	81	0	0	0

ASC by value 2 and then ASC by Value 1.

Example sequence of semester column is like below:

NSPP01 2110
NSPP02 2110
NSPP01 2111
NSPP02 2111
NSPP01 2112
NSPP02 2112
NSPP01 2201
NSPP02 2201
NSPP01 2202
NSPP02 2202
NSPP01 2203
NSPP02 2203
and so on

Check this screenshot:

Screenshot of query and its result

It's a bad idea to store the two values in one column, if you are interested in its separate parts. Change your table design accordingly and you have solved the problem for good. — Thorsten Kettner, Nov 23 '21 at 07:30

score 1 · Accepted Answer · answered Nov 23 '21 at 07:25

Im not sure on your reasons or the logic behind the column but ideally, you'd have these as 2 separate columns. It makes things like this way easier.

That being said, i reckon there are 2 ways to approach this.

If your 2 values will never change in length i.e.the values NSPP02 will always containg 5 characters and the 2203 values will always contain 4 characters then you could do something like:

SELECT LEFT(COLUMN_1,5) AS VALUE 1
SELECT RIGHT(COLUMN_1,4) AS VALUE 2
ORDER BY VALUE 2, VALUE 1

Alternatively if there is room for the number of characters in each value to increase then you'll need to use something like the SUBSTRING function. There are some good details here:

score 0 · Answer 2 · answered Nov 23 '21 at 07:35

You can actually split the semester column first and SORT, then you can suppress those helper columns to get desired output:

SELECT Semester, TotalRegistrations, TotalRegistered, Appeared, Failed, Passed
FROM(
SELECT pdc.semester as Semester,count(pdc.CNIC) as 
       TotalRegistrations,count(s.studentid) as TotalRegistered,
       count(case when r.Grade is not null then 'Appeared' end) as Appeared,
       count(case when r.Grade='f' then 'fail' end) as Failed,
       count(case when r.grade <>'f' then 'pass' end) as Passed,
       TRIM(LEFT(pdc.semester, CHARINDEX(' ', pdc.semester) - 1)) as frst, 
       TRIM(RIGHT(pdc.semester, LEN(pdc.semester) - CHARINDEX(' ', pdc.semester))) as sec
FROM PDC_PreRegistration pdc 
LEFT JOIN Students s on pdc.Semester=s.CurSemester and s.nic=pdc.CNIC
LEFT JOIN studentresultnet r on s.studentid=r.studentid and s.CurSemester=r.Semester 
LEFT JOIN semester se on se.Semester=r.Semester
WHERE pdc.semester
IN (select CurSemester from students where  batch='PD2110')
GROUP BY pdc.Semester
)
ORDER BY sec, frst

SQL order by ASC Second(value) space ASC First(value) in same column in aggregate SQL query

2 Answers2