Question 2296 in Wordpress Question posted in
The official Wordpress documentation can be found here.

SQL order by letters then numbers (DESCENDING) – WordPress

For a wordpress website that I am developing I am trying to order a list of objects in SQL by price and would like to have the order descending but have prices that are not numbers (Price on demand for example) on top.

Here’s an example of the list I need:

List of objects (DESCENDING PRICE ORDER)
Price on demand
Price on demand
10000
5000
300

Right now if the order is DESC the text prices are on the bottom of the list. And with ASC they are on top but after that it’s the lowest prices.I also tried to sort them as text but then it’s alphabetically and looks at all digits independently.

Tags:

2

Answers


  1. 0

    You could try something like this:

    SELECT fieldName FROM tableName
ORDER BY
CASE
    WHEN SUBSTR(fieldName, 1, 1) BETWEEN '0' AND '9' THEN 'B'
    ELSE 'A'
END ASC, fieldName DESC
    Login or Signup to reply.
  2. 0

    You can apply a sorting using CASE WHEN and convert numeric entries to a number. The not numeric entries will be sorted as string and occur before the numbers.

    To convert them, you can just write something like +0 and check whether this is > 0:

    SELECT 
yourcolumn
FROM yourtable
ORDER BY 
CASE WHEN yourcolumn+0 > 0 
THEN 1 ELSE 0 END, yourcolumn+0 DESC;

    This might produce another order than intended if your string contains both numbers and letters. If this is the case, you should please edit your question and point out how you want to sort such entries. A side note: This will not sort the 0 as requested 😉

    Since the above query might make some "unnatural" impression, you could also apply a regex instead which checks whether your string is numeric. The correct syntax for this will differ depending on the DB type you use.
    Here is an example for MYSQL and MariaDB:

    SELECT 
yourcolumn
FROM yourtable
ORDER BY 
CASE WHEN yourcolumn REGEXP '^[0-9]+$' 
THEN 1 ELSE 0 END, yourcolumn+0 DESC;

    A side note: This will put the zero at the end like requested 😉

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search