1

I have the data:

CREATE TABLE IF NOT EXISTS `sort` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `value` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
);

INSERT INTO `sort` (`id`, `value`) VALUES
    (1, 'abc2'),
    (2, 'abc20'),
    (3, 'abc1'),
    (4, 'abc10'),
    (5, 'abc3');

I want to have all the rows starting from a specified id then to the end of that result set to be added all the rows until that specified id and all sorted by value.

So i come out with this:

SET @id=3;
SELECT * FROM sort
ORDER BY 
id=@id DESC, value>=(SELECT value FROM sort WHERE id=@id) DESC, value ASC;

Everything works fine but is not a natural sort. With the query above i get the result abc1, abc10, abc2, abc20, abc3. The result I'm looking for is abc1, abc2, abc3, abc10, abc20. And of course if I change @id=4 the results should be abc10, abc20, abc1, abc2, abc3.

With the query bellow I get the natural sorting I want. 

SELECT * FROM sort ORDER BY LENGTH(value), value;

So the question is: How can I combine the two ORDER BY clauses into one?

Alqin
  • 1,305
  • 3
  • 16
  • 35
  • 1
    define "natural sorting", what is the expected result – RichardTheKiwi Apr 19 '11 at 10:35
  • This is a "natural sorting": "-10, -2.11, -2.10, -1, 0, abc1, abc001, abc2, abc10". If you run both SELECT query the result from the first is a natural sorting and the second is not. I am quite happy with the result from the first SELECT query so I just want to combine the two SELECT query into the second one. – Alqin Apr 19 '11 at 10:57
  • How about ab10 and abc2? – RichardTheKiwi Apr 19 '11 at 10:58
  • Have a look at: [Natural Sort in MySQL](http://stackoverflow.com/questions/153633/natural-sort-in-mysql) – RichardTheKiwi Apr 19 '11 at 11:01
  • I don't care of that much of a detail. Probably the rigth order will by ab10, abc2 – Alqin Apr 19 '11 at 11:02
  • I've read that but my biggest problem is to combine the two SELECT query into the last one, the two ORDER BY clauses into the last SELECT query. – Alqin Apr 19 '11 at 11:05

1 Answers1

0

This should work:

SELECT * 
FROM sort 
ORDER BY  
    id=@id DESC,
    LENGTH(value),
    value>=(SELECT value FROM sort WHERE id=@id) DESC, 
    value ASC;`
JohnL
  • 216
  • 1
  • 3
  • Does not work. The results should be: if(id==1){abc2, abc3, abc10, abc20, abc1} if(id==2){abc20, abc1, abc2, abc3, abc10} if(id==3){abc3, abc10, abc20, abc1, abc2} if(id==4){abc10, abc20, abc1, abc2, abc3} if(id==5){abc3, abc10, abc20, abc1, abc2} – Alqin Jun 01 '11 at 10:40