Can I order mysql starting at a given number?

Question

Hi, I have many rows in a mysql table. There is an order_id column. I want to order by two columns, first by order_id and next by id(auto increment). The problem is that i entered 1, 2, 3, 4, 5... for order_id and leave blank for the others. In my example i want to display first the rows that has the numbers 1, 2, 3, 4 but instead it shows the rows with blank order_id since that is less than 1. Is there a way to start the order at 1?

Example:

id | order_id | name
1  | NULL     | test name 1
2  | 1        | test name 2
3  | NULL     | test name 3
4  | 2        | test name 4
5  | 3        | test name 5

I would like my order statement to give the following result

test name 2
test name 4
test name 5
test name 1
test name 3

As you can see from the example i first order by the "order_id" column starting at 1

Thanks for any help.

Answer 1

You could try to enhance you query with a ifnull function. So you could check if order_id is null when it is order by autoincrement else order by order id See the documentation of mysql: http://dev.mysql.com/doc/refman/4.1/en/control-flow-functions.html

Answer 2

If you want to omit rows with order_id, then just add a where clause (where order_id is not null). Otherwise, try adding a column to the select like this:

if(order_id IS NULL or order_id='', 1, 0)

and then sort ASC on this column first, which will put non-null order_id first.

Answer 3

Are the missing values represented with NULL? If so then something like this should do the trick:

SELECT *
FROM your_table
ORDER BY IF(order_id IS NULL, 1, 0), order_id, id

If the missing values aren't NULL then you'll need to alter your IF clause accordingly. For example, if the missing fields are actually empty strings then you would probably use something like this instead:

SELECT *
FROM your_table
ORDER BY IF(order_id = '', 1, 0), order_id, id