Your first problem is the order not having backticks you have to do ORDER BY `order`, title or you'll get a sql error.
But the other thing you'll run into is that null evaluates to les than any int, meaning the null rows will come before your numbered rows, however you can't just switch to ORDER BY `order` DESC, title because than you're order will be backwards What you need to do is trick the order evaluation like so ORDER BY (0 - `order`) DESC, title This lists by increasing order, then null by increasing title as now the smallest values of `order` will now be the biggest Alternatively You could do two selects, one ordering ascending on not null columns UNION'd with a select ordered by title with order null I don't know which would be faster. -----Original Message----- From: Mike Johnson [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 04, 2004 10:43 AM To: Eve Atley; [EMAIL PROTECTED] Subject: RE: Need correct 'order by' syntax where field does not contain "NULL" From: Eve Atley [mailto:[EMAIL PROTECTED] > Hi. I had a MySQL DB set up and recently added a field > 'order' to allow for exceptions in a web site menu > heirarchy. Fields should be ordered by 'order' field > first where it does not contain 'NULL', and then by > field 'title'. I had this previously: > > select * from navigation WHERE id = '".$category."' AND > active='y' ORDER BY title > > ...and now, when I put in: > > select * from navigation WHERE id = '".$category."' AND > active='y' ORDER BY order, title > > ...my menu shows nothing. How can I write my statement to > allow for ORDER by order where 'order' does not contain > 'NULL', and then title? I'm curious about what you're using for a MySQL client. Are you using a web-based tool such as phpMyAdmin? If so, it probably added this new column using backticks, which allowed a reserved word (order) to be used as the name. I'm fairly certain that's why your menu is not displaying anything -- your query is dying when it gets to the ORDER BY clause. So first step, in your query, add backticks around order: ORDER BY `order`, title That'll allow MySQL to see it as a column name and not a reserved word. My second point is that I saw two replies to this, both of which said something along the lines of: SELECT * FROM navigation WHERE id = '".$category."' AND active='y' AND order IS NOT NULL ORDER BY order, title ...which is blatantly wrong. You asked for all records matching your original WHERE clauses, order first by the 'order' column if not null, and then by 'title' if 'order' is null. This query above will not return records for which 'order' is null. While it may not be the best way to go about it, give this a shot: SELECT *, IF(`order` IS NULL, 99, `order`) AS sort_ord FROM navigation WHERE id = '".$category."' AND active='y' ORDER BY sort_ord ASC, title This generates a temporary column in your result set that is used as the sort order based on the values of 'order.' Note that if you use values higher than 99 in 'order,' you'll want to set the 99 higher. For an example, try this out: === mysql> CREATE TABLE test (title VARCHAR(10), `order` INT); Query OK, 0 rows affected (0.01 sec) mysql> INSERT INTO test (title, `order`) VALUES ('a', NULL); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO test (title, `order`) VALUES ('b', NULL); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO test (title, `order`) VALUES ('c', 1); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO test (title, `order`) VALUES ('d', NULL); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO test (title, `order`) VALUES ('e', 3); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO test (title, `order`) VALUES ('f', 2); Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM test; +-------+-------+ | title | order | +-------+-------+ | a | NULL | | b | NULL | | c | 1 | | d | NULL | | e | 3 | | f | 2 | +-------+-------+ 6 rows in set (0.00 sec) mysql> SELECT *, IF(`order` IS NULL, 99, `order`) AS sort_ord FROM test ORDER BY sort_ord ASC, title ASC; +-------+-------+----------+ | title | order | sort_ord | +-------+-------+----------+ | c | 1 | 1 | | f | 2 | 2 | | e | 3 | 3 | | a | NULL | 99 | | b | NULL | 99 | | d | NULL | 99 | +-------+-------+----------+ 6 rows in set (0.00 sec) === If someone knows a better way to do this, I'd be curious to hear it. I imagine there's a more efficient way to do it, but I can't seem to stumble upon it. Anyway, hope this helps. :) -- Mike Johnson Web Developer Smarter Living, Inc. phone (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
