ORDER BY clause fails to do descending order on this field column

SELECT s.id, s.first_name, s.last_name,
IF(s.school_year_id = 0, s.school_year_other,
y.school_year_alt_display) AS school_year_name
FROM student s
LEFT OUTER JOIN school_year y ON s.school_year_id = y.id
ORDER BY upper(school_year_name) desc, upper(s.last_name) asc,
upper(s.first_name) asc

This query produces records based upon either the column
s.school_year_id being 0 and s.school_year_other having a value, or
s.school_year_id having a matching ID within school_year y

The records come back fine, and I can order them logically if I do this

ORDER BY upper(school_year_name) asc..

However, if I change "asc" to "desc", the order is wrong, it's not
truly in alphabetical descending order every time.

Could someone shed some light on this?

Thanx
Phil

Re: ORDER BY clause fails to do descending order on this field column

phillip.s.powell@gmail.com wrote:
> SELECT s.id, s.first_name, s.last_name,
> IF(s.school_year_id = 0, s.school_year_other,
> y.school_year_alt_display) AS school_year_name
> FROM student s
> LEFT OUTER JOIN school_year y ON s.school_year_id = y.id
> ORDER BY upper(school_year_name) desc, upper(s.last_name) asc,
> upper(s.first_name) asc
>
> This query produces records based upon either the column
> s.school_year_id being 0 and s.school_year_other having a value, or
> s.school_year_id having a matching ID within school_year y
>
> The records come back fine, and I can order them logically if I do this
>
> ORDER BY upper(school_year_name) asc..
>
> However, if I change "asc" to "desc", the order is wrong, it's not
> truly in alphabetical descending order every time.
>
> Could someone shed some light on this?
>
> Thanx
> Phil
>

Could this be because the sort routine first prints the shorter and then
the longer name if they start with the same charachter...

So ascending:

Alp
Alpha
...
....
Zul
Zulu

And Descending:

Zul
Zulu
....
....
Alp
Alpha

It might help if you could post (a sample of) the results of both
queries as well. Remember text sorting works differently than number
sorting as it is tricky to decide if Alpha should be greater or smaller
than Alp as this is not so strictly defined for text as it is for numbers.

Jonathan

Re: ORDER BY clause fails to do descending order on this field column

mysql> select s.student_first_name, s.student_last_name,
if(s.school_year_id = 0, s.school_year_other,
y.school_year_alt_display) as school_year_name from student s left
outer join school_year y on s.school_year_id = y.id where s.id in (7,
12, 32, 69) order by upper(school_year_name) asc;
+--------------------+-------------------+------------------ --------+
| student_first_name | student_last_name | school_year_name |
+--------------------+-------------------+------------------ --------+
| John | Smith | applied for grad. school |
| Bob | Doe | Graduate |
| Suzie | Cue | Senior |
| Artemesia | Jackson | Senior |
+--------------------+-------------------+------------------ --------+
4 rows in set (0.01 sec)

mysql> select s.student_first_name, s.student_last_name,
if(s.school_year_id = 0, s.school_year_other,
y.school_year_alt_display) as school_year_name from student s left
outer join school_year y on s.school_year_id = y.id where s.id in (7,
12, 32, 69) order by upper(school_year_name) desc;
+--------------------+-------------------+------------------ --------+
| student_first_name | student_last_name | school_year_name |
+--------------------+-------------------+------------------ --------+
| Suzie | Cue | Senior |
| Artemesia | Jackson | Senior |
| John | Smith | applied for grad. school |
| Bob | Doe | Graduate |
+--------------------+-------------------+------------------ --------+
4 rows in set (0.01 sec)

---------------------------------------------------------

Not sure if that will help but there you go!

Phil

Jonathan wrote:
> phillip.s.powell@gmail.com wrote:
> > SELECT s.id, s.first_name, s.last_name,
> > IF(s.school_year_id = 0, s.school_year_other,
> > y.school_year_alt_display) AS school_year_name
> > FROM student s
> > LEFT OUTER JOIN school_year y ON s.school_year_id = y.id
> > ORDER BY upper(school_year_name) desc, upper(s.last_name) asc,
> > upper(s.first_name) asc
> >
> > This query produces records based upon either the column
> > s.school_year_id being 0 and s.school_year_other having a value, or
> > s.school_year_id having a matching ID within school_year y
> >
> > The records come back fine, and I can order them logically if I do this
> >
> > ORDER BY upper(school_year_name) asc..
> >
> > However, if I change "asc" to "desc", the order is wrong, it's not
> > truly in alphabetical descending order every time.
> >
> > Could someone shed some light on this?
> >
> > Thanx
> > Phil
> >
>
> Could this be because the sort routine first prints the shorter and then
> the longer name if they start with the same charachter...
>
> So ascending:
>
> Alp
> Alpha
> ..
> ...
> Zul
> Zulu
>
> And Descending:
>
> Zul
> Zulu
> ...
> ...
> Alp
> Alpha
>
> It might help if you could post (a sample of) the results of both
> queries as well. Remember text sorting works differently than number
> sorting as it is tricky to decide if Alpha should be greater or smaller
> than Alp as this is not so strictly defined for text as it is for numbers.
>
> Jonathan