Re: Need correct 'order by' syntax where field does not contain "NULL"

[Michael Stassen]

Mike Johnson wrote:

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?

<snip>
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.
<snip>
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.
ASC (ascending) is the default order, so you can leave it out.  That's not 
better or more efficient, it just saves typing.

Instead of guessing a max value, such as 99, why not use the max value for 
the type of the column to be *sure* the NULLs come last.  So, use 127 if 
`order` is a tinyint, 255 for tinyint unsigned, and so on.  I'll assume 
`order` is a tinyint and use 127 in my example below.

You could accomplish the same thing without the extra sort_ord column by 
moving your IF clause to the ORDER BY clause, like this:

  SELECT * FROM test ORDER BY IF(`order` IS NULL, 127, `order`), title;
  +-------+-------+
  | title | order |
  +-------+-------+
  | c     |     1 |
  | f     |     2 |
  | e     |     3 |
  | a     |  NULL |
  | b     |  NULL |
  | d     |  NULL |
  +-------+-------+
That's not any better efficiency-wise, but it saves the extra output.

======

Is there a better way?  Probably.  I'd start by renaming the column to 
something that isn't a reserved word, menu_ord perhaps, so I wouldn't have 
to remember to use backticks.  Then I'd consider whether a different scheme 
of values in that column would work better.  In general, you want to avoid 
filtering and/or sorting on a function of a column, if possible, because 
then an index on the column can't be used.

You want a custom sort order which is almost, but not quite, alphabetical by 
title.  As I see it, you've created a new column to hold the desired 
ordering, but you're only partly using it.  Instead of checking for NULLs 
when you order your select, why not replace the NULLs with something useful 
ahead of time?  Assuming the number of exceptions to be sorted first is less 
than 255, I'd do the following:

  UPDATE navigation SET `order`=255 WHERE `order` IS NULL;
  ALTER TABLE navigation
    CHANGE `order` menu_ord TINYINT UNSIGNED NOT NULL DEFAULT 255;
Then you can simply

  SELECT * FROM navigation
  WHERE id = '".$category."' AND active='y'
  ORDER BY menu_ord, title
Alternatively, if this table is relatively static, it may be practical to 
assign appropriate values to menu_ord for every row.  Then you could simply 
ORDER BY menu_ord.

Michael

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]