Raul77
asked on
Sorting Birthdays not working !
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,
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,
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.
Guess I got caught day dreaming too long before clicking on submit. Sorry 8080.
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)
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)
ASKER
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SELECT * FROM birthdays ORDER BY Bmonth, Bday
Since ORDER BY allows for subsorts it is this easy :)
SELECT * FROM birthdays ORDER BY Bmonth, Bday
ASKER
Thank you,
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.