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]
RE: Need correct 'order by' syntax where field does not contain NULL
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]
Re: Need correct 'order by' syntax where field does not contain NULL
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]
Re: Need correct 'order by' syntax where field does not contain NULL
select * from navigation WHERE id = '.$category.' AND active='y' AND order IS NOT null ORDER BY order, title 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? Thanks, Eve -- 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]
RE: Need correct 'order by' syntax where field does not contain NULL
WHERE id = id = '$category.' AND active ='y' AND order is not NULL ORDER by order,title -Original Message- From: Eve Atley [mailto:[EMAIL PROTECTED] Sent: Monday, May 03, 2004 2:24 PM To: [EMAIL PROTECTED] Subject: Need correct 'order by' syntax where field does not contain NULL 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? Thanks, Eve -- 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]
