0

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user3663808
  • 43
  • 1
  • 2
  • 10
  • 1
    `C52 should come in top` - you mean bottom, right? (You are ordering `DESC`.) Or is `C52` > `C95` for other reasons? – Aaron Bertrand Mar 31 '22 at 17:53
  • There is no reason SQL Server should do this with normal sort rules. WHat you are looking for is called Natural Sort. Relevant: https://stackoverflow.com/questions/34509/natural-human-alpha-numeric-sort-in-microsoft-sql-2005 – Charlieface Mar 31 '22 at 19:24

1 Answers1

3

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
John Cappelletti
  • 79,615
  • 7
  • 44
  • 66
  • You are the MAN . It's working as I want. Thanks a lot.But when I put this query in my SSRS report ,most of the data get sorted but still few are not sorting on busniessdate. | StoreBusinessKey | BusinessDate | |-------------------|--------------| |C784 |3/15/2022| |C784 |3/31/2022| |C784 |3/22/2022| – user3663808 Apr 01 '22 at 09:10