0

I would like to order a set like this:

  • a 2
  • a 1
  • b 9

not like the normal ascending order:

  • a 1
  • a 2
  • b 9

I other words, I have a single column with some text and sometimes a number i the end. I would like to have all the fields with a number in the end to be sorted descending on the numbers, but the leading text must still be sorted ascending.

The strings are not just 1 char long but of an unknown length and the numbers are also not only 1 char long, but also of an unknown length and not guaranteed to be in the end of the string it might look like: "Random event, 12th time"

If I have a string like "Random event, 12th time" I want it to appear before: "Random event, 11th time" in my result.

Hope there are some SQL guys who know the answer to this :)

hovmand
  • 140
  • 8

2 Answers2

1

you could create a function that isolates the number you want to order by. eg. something like this (found here MySQL strip non-numeric characters to compare)

DROP FUNCTION IF EXISTS STRIP_NON_DIGIT;
DELIMITER $$
CREATE FUNCTION STRIP_NON_DIGIT(input VARCHAR(255))
   RETURNS INT
   DETERMINISTIC
BEGIN
   DECLARE output   VARCHAR(255) DEFAULT '';
   DECLARE iterator INT          DEFAULT 1;
   WHILE iterator < (LENGTH(input) + 1) DO
      IF SUBSTRING(input, iterator, 1) IN ( '0', '1', '2', '3', '4', '5', '6', '7', '8', '9' ) THEN
         SET output = CONCAT(output, SUBSTRING(input, iterator, 1));
      END IF;
      SET iterator = iterator + 1;
   END WHILE;
   RETURN output;
END
$$

and now run your sql like this:

SELECT * FROM myTable order by myString, STRIP_NON_DIGIT(myString) DESC;

Community
  • 1
  • 1
caitriona
  • 8,569
  • 4
  • 32
  • 36
-1
select * from table name where "your query " and  "  " order by asc;

or if in descending use "dec" your column which you want to descending or ascending put in " "

Nilesh patel
  • 1,216
  • 2
  • 14
  • 38
  • What is `where "your query" "`?? and what is `and " "`? you didn't compare it to anything? and `order by ` what??? – Mahmoud Gamal Oct 11 '12 at 12:16
  • $query = "SELECT `msg`, `id`, `nick`, `date` FROM `entries` WHERE `ctg` = ? ORDER BY `id` DESC LIMIT 15 OFFSET ?"; – Nilesh patel Oct 11 '12 at 18:44
  • I don't think what you are suggesting is the solution to my problem. Please read my post and the comment below again or explain me why this solves my problem. – hovmand Oct 11 '12 at 20:29