NaturalSort.sql

/*
  NATURAL ORDER in MySQL !
*/

-- Create test table
-- 

DROP TABLE IF EXISTS `natural_sort`;
CREATE TABLE IF NOT EXISTS `natural_sort` (
  `id` int(4) unsigned NOT NULL AUTO_INCREMENT,
  `val` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;



-- Fill in table with example values
--

INSERT INTO `natural_sort` (`id`, `val`) VALUES
(1, '1100W'),
(2, 'c'),
(3, '1400W'),
(4, '750W'),
(5, 'a'),
(6, '500W'),
(7, 'b');


-- Natural sort function (as on Macintosh)
--
-- first order: deal with every values as numbers
-- second order: order every row not ordered because equals (=letters)

SELECT * 
FROM `natural_sort` 
ORDER BY 
  `natural_sort`.`val` +0 , 
  `natural_sort`.`val`