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:

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.

CodePudding user response：

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

CodePudding user response：

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 ;)