MySQL Forums
Forum List  »  General

Ascending descending
Posted by: Marcelo Stucky
Date: September 26, 2006 02:12AM

Hello there
I've got a strange situation
the same query one with order by surname (default ASC)
and one with order by surname DESC
and get 2 and 3 records respectively


it has definatly a relation to the order by clause but why?



mysql> SELECT p.p_id,p.surname,p.name,p.company ,t1.telefon as telhome,t2.telefon as telwork FROM persons p LEFT JOIN contact t1 on p.p_id=t1.p_id and t1.con_Type='work' LEFT JOIN contact t2 on p.p_id=t2.p_id and t2.con_Type='home' WHERE p.surname like 'stu%' ORDER BY p.surname;
+------+-------------+-----------+-------------------+-------------------+----------------+
| p_id | surname | name | company | telhome | telwork |
+------+-------------+-----------+-------------------+-------------------+----------------+
| 1603 | Stub##### | #ohn E.G. | #############ITED | ########### 9111 | NULL |
| 36 | Stuc### | #ar## | #### | ########## 123 | ###########-55 |
+------+-------------+-----------+-------------------+-------------------+----------------+

and now ordering DESC
retrives one record more (p_id=5520)


mysql> SELECT p.p_id,p.surname,p.name,p.company ,t1.telefon as telhome,t2.telefon as telwork FROM persons p LEFT JOIN contact t1 on p.p_id=t1.p_id and t1.con_Type='work' LEFT JOIN contact t2 on p.p_id=t2.p_id and t2.con_Type='home' WHERE p.surname like 'stu%' ORDER BY p.surname DESC;
+------+-------------+-----------+---------------------------------------------+-----------------------+----------------+
| p_id | surname | name | company | telhome | telwork |
+------+-------------+-----------+---------------------------------------------+-----------------------+----------------+
| 5520 | Stum### | #eter | ########################################mbH | ################-2901 | NULL |
| 36 | Stuc### | #ar## | #### | ########## 123 | ###########-55 |
| 1603 | Stub######n | #ohn E.G. | ###############ED | ############ 9111 | NULL |
+------+-------------+-----------+---------------------------------------------+-----------------------+----------------+



maybe charset?
there are some surnames with German Ü between this records like surname= Stülmer

show variables like 'char%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

show variables like 'version';
+---------------+------------------------------+
| Variable_name | Value |
+---------------+------------------------------+
| version | 5.0.22-Debian_1.dotdeb.1-log |
+---------------+------------------------------+





Many thanks
Marcelo



Edited 2 time(s). Last edit at 09/27/2006 12:27AM by Marcelo Stucky.

Options: ReplyQuote


Subject
Written By
Posted
Ascending descending
September 26, 2006 02:12AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.