Re: sort order
Hi Wes, have you double checked you character set and collation ??
http://dev.mysql.com/doc/refman//5.5/en/charset-charsets.html
The collation is which determines the sorting order.
Carlos Proal
On Wed, May 30, 2012 at 4:02 PM, Wes James compte...@gmail.com wrote:
I have mysql 5.1.62-0ubuntu0.11.10.1 installed, but the sort order for
utf8-unicode doesn't seem to be right. It is sorting some text like this
(order by title):
!
! *`-=[];',./~@#$%^()_+{}|:?\
!
![
!@
!a
!A
!t
!test
'Coal age'
[
\--\
100 years
when it should be (shouldn't it?):
!
! *`-=[];',./~@#$%^()_+{}|:?\
!
!@
!A
![
!a
!t
!test
'Coal age'
100 years
[
\--\
--
Why is it doing this?
Thanks,
-wes
Re: sort order
I'll double check and get back.
Thanks,
-wes
On Wed, May 30, 2012 at 3:43 PM, Carlos Proal carlos.pr...@gmail.comwrote:
Hi Wes, have you double checked you character set and collation ??
http://dev.mysql.com/doc/refman//5.5/en/charset-charsets.html
The collation is which determines the sorting order.
Carlos Proal
On Wed, May 30, 2012 at 4:02 PM, Wes James compte...@gmail.com wrote:
I have mysql 5.1.62-0ubuntu0.11.10.1 installed, but the sort order for
utf8-unicode doesn't seem to be right. It is sorting some text like this
(order by title):
!
! *`-=[];',./~@#$%^()_+{}|:?\
!
![
!@
!a
!A
!t
!test
'Coal age'
[
\--\
100 years
when it should be (shouldn't it?):
!
! *`-=[];',./~@#$%^()_+{}|:?\
!
!@
!A
![
!a
!t
!test
'Coal age'
100 years
[
\--\
--
Why is it doing this?
Thanks,
-wes
Re: sort order
In phpMyAdmin it says the database collation is utf8_unicode_ci and each of
the columns is utf8_unicode_ci except the ID primary key column.
If you create a simple table with ID and TITLE and put the data below (each
row in a record) and run 'select * from table order by title' what do you
get?
CREATE TABLE IF NOT EXISTS `testtab` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` text COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ;
The above is a sample export of what I have for the table definition.
-wes
On Wed, May 30, 2012 at 3:43 PM, Carlos Proal carlos.pr...@gmail.comwrote:
Hi Wes, have you double checked you character set and collation ??
http://dev.mysql.com/doc/refman//5.5/en/charset-charsets.html
The collation is which determines the sorting order.
Carlos Proal
On Wed, May 30, 2012 at 4:02 PM, Wes James compte...@gmail.com wrote:
I have mysql 5.1.62-0ubuntu0.11.10.1 installed, but the sort order for
utf8-unicode doesn't seem to be right. It is sorting some text like this
(order by title):
!
! *`-=[];',./~@#$%^()_+{}|:?\
!
![
!@
!a
!A
!t
!test
'Coal age'
[
\--\
100 years
when it should be (shouldn't it?):
!
! *`-=[];',./~@#$%^()_+{}|:?\
!
!@
!A
![
!a
!t
!test
'Coal age'
100 years
[
\--\
--
Why is it doing this?
Thanks,
-wes
RE: sort order
a and A are identical, according to utf8_unicode_ci and utf8_general_ci,
but not utf8_bin.
That is, I would not expect [ to slip in between them.
About 80% of the way down this page, there is a list of some of the
'equivalences' in selected collations:
http://mysql.rjweb.org/doc.php/charcoll
In particular:
utf8 : utf8_unicode_ciA=a=ª=À=Á=Â=Ã=Ä=Å=à=á=â=ã=ä=å ae az Æ=æ
says that lots of things collate equal to 'A'. All of them sort before ae az
the ligature Æ (upper or lower case)
-Original Message-
From: Wes James [mailto:compte...@gmail.com]
Sent: Wednesday, May 30, 2012 2:03 PM
To: mysql general discussion
Subject: sort order
I have mysql 5.1.62-0ubuntu0.11.10.1 installed, but the sort order for
utf8-unicode doesn't seem to be right. It is sorting some text like
this (order by title):
!
! *`-=[];',./~@#$%^()_+{}|:?\
!
![
!@
!a
!A
!t
!test
'Coal age'
[
\--\
100 years
when it should be (shouldn't it?):
!
! *`-=[];',./~@#$%^()_+{}|:?\
!
!@
!A
![
!a
!t
!test
'Coal age'
100 years
[
\--\
--
Why is it doing this?
Thanks,
-wes
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: Sort order
At 5:52 PM -0700 9/14/03, Scott Haneda wrote: Im having a little trouble getting sorting to do what I want... describe resources; +-+---+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+---+--+-+-++ | id | int(11) | | PRI | NULL| auto_increment | | title | varchar(64) | | | || | url | varchar(255) | | | || | location| varchar(64) | | | || | category| varchar(128) | | | || | description | text | | | || | prefer | char(1) | | | 0 || | sort_order | int(1)| | | 9 || | status | char(1) | | | 0 || | updated | timestamp(14) | YES | | NULL|| | added | timestamp(14) | YES | | NULL|| +-+---+--+-+-++ My sql is order by sort_oder ASC, added; At first, I had no value set for the default in sort_order, but those came up first. I am looking to get a ascending sort order that will ignore the reocrd, or basically shove it to the end of the sort if it is empty or null. sort_order is an INT column. What do you mean by it being empty? Can this be done? Or is my only option in this case to set it to 9 to force those records to the end? I could not seem to alter the field to get it to change them all to null, so I was unable to test if null is sorted first or last, or ignored altogether. The behavior has changed for various versions of MySQL, unfortunately: http://www.mysql.com/doc/en/Problems_with_NULL.html You can force NULL values to be sorted to either end by adding another sort column: ORDER BY IF(sort_order IS NULL,1,0), sort_order, added; That'll sort NULL values at the end. Use IF(sort_ORDER IS NULL,0,1) to sort them at the beginning instead. -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sort order
on 09/14/2003 06:14 PM, Paul DuBois at [EMAIL PROTECTED] wrote: You can force NULL values to be sorted to either end by adding another sort column: ORDER BY IF(sort_order IS NULL,1,0), sort_order, added; That'll sort NULL values at the end. Use IF(sort_ORDER IS NULL,0,1) to sort them at the beginning instead. The interface I have made for adding records is probably not idea, I have a input type = text html field, if I leave it blank, NULL does not get inserted, rather a 0 does. I have the default set to '9' now. I would have to make alterations to my admin interface to allow NULL to be input on new record creation. I don't really want to do this. Is there any way to have a ASC sort order that sends 0 to the end, and sorts on 1-9? I know a strange request, but this would be simplest in my case. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED]Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sort order
on 09/14/2003 06:14 PM, Paul DuBois at [EMAIL PROTECTED] wrote: ORDER BY IF(sort_order IS NULL,1,0), sort_order, added; select title, sort_order from resources order by IF(sort_order IS NULL,1,0) sort_order ASC limit 5; ERROR 1064: You have an error in your SQL syntax near 'sort_order ASC limit 5' at line 1 Any ideas? -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED]Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sort order
At 6:27 PM -0700 9/14/03, Scott Haneda wrote: on 09/14/2003 06:14 PM, Paul DuBois at [EMAIL PROTECTED] wrote: You can force NULL values to be sorted to either end by adding another sort column: ORDER BY IF(sort_order IS NULL,1,0), sort_order, added; That'll sort NULL values at the end. Use IF(sort_ORDER IS NULL,0,1) to sort them at the beginning instead. The interface I have made for adding records is probably not idea, I have a input type = text html field, if I leave it blank, NULL does not get inserted, rather a 0 does. I have the default set to '9' now. I would have to make alterations to my admin interface to allow NULL to be input on new record creation. I don't really want to do this. Okay, but you were the one who (in your previous message) stated that you wanted to know how to sort NULL at one end or the other... Is there any way to have a ASC sort order that sends 0 to the end, and sorts on 1-9? I know a strange request, but this would be simplest in my case. It's not really *that* strange, and you can use the same trick described above with a little modification: ORDER BY IF(sort_order = 0,1,0), sort_order, added -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sort order
At 6:33 PM -0700 9/14/03, Scott Haneda wrote: on 09/14/2003 06:14 PM, Paul DuBois at [EMAIL PROTECTED] wrote: ORDER BY IF(sort_order IS NULL,1,0), sort_order, added; select title, sort_order from resources order by IF(sort_order IS NULL,1,0) sort_order ASC limit 5; ERROR 1064: You have an error in your SQL syntax near 'sort_order ASC limit 5' at line 1 Any ideas? Missing comma. Look closely. -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
