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

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?

CodePudding user response：

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