Archive for October, 2008

ORDER BY (the lesser known way)

Monday, October 27th, 2008

We all know with MySQL you can use ORDER BY with a list of columns to return an ordered set, e.g. ORDER BY name, type, state;
I often use the syntax ORDER BY 1,2; which I’m surprised that some people do not know about.

However I needed to do some selective ordering of a type field, I didn’t want to for example have a lookup table just to join for ordering. While contemplating a means of achieving this, I asked a work colleague, who I figured may have just experienced this problem before. Lone behold I became the student as I discovered there is a third syntax with ORDER BY, using expressions.

mysql> create table test(name varchar(10) not null, type varchar(10) not null);
Query OK, 0 rows affected (0.06 sec)

mysql> insert into test(name,type) values
('Apples','Fruit'),
('Bananas','Fruit'),
('Carrots','Veg'),
('Onions','Veg'),
('Beer','Liquid'),
('Water','Liquid'),
('Crackers','Food');
Query OK, 7 rows affected (0.00 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> select name from test
order by type='Veg' DESC,
         type='Fruit' DESC,
         type='Food' DESC,
         type='Liquid' DESC;
+----------+
| name     |
+----------+
| Carrots  |
| Onions   |
| Apples   |
| Bananas  |
| Crackers |
| Beer     |
| Water    |
+----------+
7 rows in set (0.00 sec)

Of course, reading the MySQL Manual confirms this on the SELECT command.
I’ve not read the MySQL manual from cover to cover, since 4.x days. Perhaps it’s time.

Thanks to Nick Pisarro of Blog Revolution for this most valuable tip.

Javascript Helpers

Monday, October 13th, 2008

Combined with my old favorites of Dynamic Drive, DHTML Goodies and Brain Jar, I’ve added the following to my list of Javascript sources.