Order by not giving expected results in SQL Server select

Question

I have select statement table with two columns, storebusinesskey and businessdate.

When I run a select and try to sort the output by storebusinesskey, it's not working.

SELECT
    t1.StoreBusinessKey, DateBusinessKey AS BusinessDate
FROM
    TABA t1
LEFT JOIN 
    TABB t2 ON t1.StoreBusinessKey = t2.StoreBusinessKey 
            AND t1.DateBusinessKey = t2.BusinessDate
WHERE
    t2.BusinessDate IS NULL
ORDER BY
    t1.StoreBusinessKey DESC

Output

StoreBusinessKey	BusinessDate
C95	2022-03-15
C91	2022-03-27
C89	2022-03-09
C784	2022-03-22
C784	2022-03-15
C766	2022-03-22
C766	2022-03-29
C759	2022-03-22
C693	2022-03-22
C681	2022-03-22
C674	2022-03-30
C654	2022-03-30
C606	2022-03-30
C595	2022-03-29
C595	2022-03-30
C56	2022-03-30
C521	2022-03-30
C52	2022-03-30
C486	2022-03-27
C486	2022-03-30
C486	2022-03-26
C486	2022-03-29
C486	2022-03-28
C476	2022-03-29
C476	2022-03-30
C471	2022-03-30

As you can see C52 should come in top but it's not. Can you please help me to sort this output by Storebusinesskey first and then by businessdate?

Answer 1

Your sorting on the string value. Assuming one leading alpha and with a little string manipulation, we can easily convert to an int and sort

Example

Declare @YourTable Table ([StoreBusinessKey] varchar(50),[BusinessDate] varchar(50))
Insert Into @YourTable Values 
 ('C95','2022-03-15')
,('C91','2022-03-27')
,('C89','2022-03-09')
,('C784','2022-03-22')
,('C784','2022-03-15')
,('C52','2022-03-30')   -- Should be first
 
Select * 
 From  @YourTable
 Order By try_convert(int,substring([StoreBusinessKey],2,25))
         ,[BusinessDate]

Results

StoreBusinessKey    BusinessDate
C52                 2022-03-30
C89                 2022-03-09
C91                 2022-03-27
C95                 2022-03-15
C784                2022-03-15
C784                2022-03-22