Sorting Birthdays not working !

Raul77
Raul77 used Ask the Experts™
on
I have a simple DB like

ID     Name                      Bmonth               Bday           Birthday
1      Joe Smith                9                          3                 Sept, 3rd
2      Jack Smith               8                         8                 August, 8th
3      Jill Smith                   7                         22              July, 7th
4      Joan Smith               8                          16             August, 16th

Now when i do a select statement and then sort my results like this :

ORDER BY (CASE WHEN Bmonth = @month THEN 1 WHEN Bmonth > @month THEN 2 WHEN Bmonth < @month THEN 3 ELSE 100 END), Bday

my output is
3      Jill Smith                   7                         22              July, 7th
1      Joe Smith                9                          3                 Sept, 3rd
2      Jack Smith               8                         8                 August, 8th
4      Joan Smith               8                          16             August, 16th

@moth = todays month (for example now is 7 for july)

why am i getting SEPTEMBER before August?

Thanks,
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
The short answer is that you told the SQL to do that. ;-)
The long answer is that you have the data sorted first by you BMonth setting, which assigns the July date a BMonth "score" of 1 and the other entries a scoe of 2, so far so good.  Next you sort by the day of the month within that.  Let me provide an expanded set of data results that includes the Score between the BMonth and the BDay columns:
ID    Name          Bmonth SCORE Bday Birthday
3     Jill Smith       7            1             22      July, 7th
1     Joe Smith      9            2             3        Sept, 3rd
2     Jack Smith     8            2            8         August, 8th
4     Joan Smith    8            2            16       August, 16th
 
Look at the Score and BDay columns.  See the reason now?  They are sorted by BDay within Score . . . just like you asked it to. ;-)
Now you know why dates should be stored as dates!  If you add a DateOfBirth column that is actually a date or datetime datatyp, you could sort by the Score and then by the DateOfBirth formatted as MMDD and get what you are really looking for.
Kevin CrossChief Technology Officer
Most Valuable Expert 2011

Commented:
How do you want the data sorted exactly as your issue is because you are doing your secondary sort on day, so it is ordered 3, 8, 16.
Kevin CrossChief Technology Officer
Most Valuable Expert 2011

Commented:
Guess I got caught day dreaming too long before clicking on submit.  Sorry 8080.
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Commented:
since Jill smith is the only one falling in the 1st codition (7=7) then she is first. thats ok, right?
now all the others in your list fall on the second condition (8s and 9s are > 7), and since the secondary order-by is the day, 3 comes before 8 and 16 and therefore the results are ok
which means the sql is not
if you want them ordered by current-first, then on forward in sequence of birthdays (as it seems from the syntax), then add bMonth as the secondary order-by, having the day third (only for those born on the same month)
 

Author

Commented:
mmm i see the problem now ... i want it to be sorted like this

3      Jill Smith                   7                         22              July, 7th
2      Jack Smith               8                         8                 August, 8th
4      Joan Smith               8                          16             August, 16th
1      Joe Smith                9                          3                 Sept, 3rd

is there anyway beside adding a new column?
Chief Technology Officer
Most Valuable Expert 2011
Commented:
As suggested, just put in BMonth as second sort.
ORDER BY (CASE WHEN Bmonth = @month THEN 1 WHEN Bmonth > @month THEN 2 ELSE 100 END), BMonth, Bday

Open in new window

NerdsOfTechTechnology Scientist

Commented:

SELECT * FROM birthdays ORDER BY Bmonth, Bday

Open in new window

NerdsOfTechTechnology Scientist

Commented:
Since ORDER BY allows for subsorts it is this easy :)
SELECT * FROM birthdays ORDER BY Bmonth, Bday

Open in new window

Author

Commented:
Thank you,

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial