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

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:

I want the result to be like this:

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

CodePudding user response：

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:

CodePudding user response：

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