XML FeedsTags: rand
Experimenting with ORDER BY
By admin on Jun 1, 2008 | In Daily Commute - Standard hints/tips | Send feedback »
Here I want to look at some different ways of sorting data, and I'll do it by looking at this table ORDERS (for this example we'll have only a few rows):
| location | amount | ord_date | order_id |
+-----------+--------+------------+----------+
| EUROPE | 5200 | 2008-03-03 | 500 |
| EUROPE | 2000 | 2008-02-21 | 450 |
| EUROPE | 13000 | 2008-04-02 | 502 |
| JAPAN | 20000 | 2008-04-03 | 503 |
| JAPAN | 13000 | 2008-05-05 | 506 |
| AMERICA | 13700 | 2008-05-29 | 507 |
| AMERICA | 10300 | 2008-02-05 | 220 |
| AUSTRALIA | 18743 | 2008-03-18 | 270 |
| AUSTRALIA | 40000 | 2008-06-02 | 700 |
| EUROPE | 35000 | 2008-06-02 | 702 |
Now, you probably know we can normally ORDER BY a field such as location (either in the default ascending mode, or by using the keyword DESC, for displaying a descending order) or by any of the other fields (indeed you can order by many fields in the same statement). You can also add WHERE clauses prior to the ORDER BY clause, so that you perform your sort on a sub-group. But, in this example, supposing you want to show the orders from America and Japan first, and then by amount descending? You can do this by the following select statement:
Code:
SELECT location, amount, | |
YEAR(ord_date) AS year, MONTH(ord_date) , | |
order_id | |
FROM ORDERS | |
ORDER BY | |
field(location, 'JAPAN', 'AMERICA') desc, amount desc; |
| location | amount | year | month | order_id |
+-----------+--------+------+-------+----------+
| AMERICA | 13700 | 2008 | 5 | 507 |
| AMERICA | 10300 | 2008 | 2 | 220 |
| JAPAN | 20000 | 2008 | 4 | 503 |
| JAPAN | 13000 | 2008 | 5 | 506 |
| AUSTRALIA | 40000 | 2008 | 6 | 700 |
| EUROPE | 35000 | 2008 | 6 | 702 |
| AUSTRALIA | 18743 | 2008 | 3 | 270 |
| EUROPE | 13000 | 2008 | 4 | 502 |
| EUROPE | 5200 | 2008 | 3 | 500 |
| EUROPE | 2000 | 2008 | 2 | 450 |
Notice I also changed the format of the ord_date field a little, to display months and years. I'm going to change the format a little more :- I want to display the months in Spanish rather than numerals. I do this here by use of the CASE function:
Code:
SELECT location, amount, | |
CASE when month(ord_date) = 1 then "enero" | |
when month(ord_date) = 2 then "febrero" | |
when month(ord_date) = 3 then "Marzo" | |
when month(ord_date) = 4 then "Abril" | |
when month(ord_date) = 5 then "Mayo" | |
ELSE "despues de Mayo" END AS trumonth, | |
YEAR(ord_date) AS year, | |
order_id | |
FROM ORDERS | |
ORDER BY | |
field(location, 'JAPAN', 'AMERICA') desc, amount desc; |
| location | amount | trumonth | year | order_id |
+-----------+--------+-----------------+------+----------+
| AMERICA | 13700 | Mayo | 2008 | 507 |
| AMERICA | 10300 | febrero | 2008 | 220 |
| JAPAN | 20000 | Abril | 2008 | 503 |
| JAPAN | 13000 | Mayo | 2008 | 506 |
| AUSTRALIA | 40000 | despues de Mayo | 2008 | 700 |
| EUROPE | 35000 | despues de Mayo | 2008 | 702 |
| AUSTRALIA | 18743 | Marzo | 2008 | 270 |
| EUROPE | 13000 | Abril | 2008 | 502 |
| EUROPE | 5200 | Marzo | 2008 | 500 |
| EUROPE | 2000 | febrero | 2008 | 450 |
Now (and here's the clever part) you can use functions performed on fields within the ORDER BY clause (including the CASE statement). Imagine you want to look at May and post-May figures first, and then see the earlier figures in location order?
Code:
SELECT location, amount, | |
MONTH(ord_date) truemonth, | |
YEAR(ord_date) AS year, | |
order_id | |
FROM ORDERS | |
ORDER BY | |
CASE when month(ord_date) > 5 then 1 | |
when month(ord_date) = 5 then 1 | |
ELSE 2 END, | |
location, | |
amount desc; |
| location | amount | truemonth | year | order_id |
+-----------+--------+-----------+------+----------+
| AMERICA | 13700 | 5 | 2008 | 507 |
| AUSTRALIA | 40000 | 6 | 2008 | 700 |
| EUROPE | 35000 | 6 | 2008 | 702 |
| JAPAN | 13000 | 5 | 2008 | 506 |
| AMERICA | 10300 | 2 | 2008 | 220 |
| AUSTRALIA | 18743 | 3 | 2008 | 270 |
| EUROPE | 13000 | 4 | 2008 | 502 |
| EUROPE | 5200 | 3 | 2008 | 500 |
| EUROPE | 2000 | 2 | 2008 | 450 |
| JAPAN | 20000 | 4 | 2008 | 503 |
You can see that you can order on the results of a function (or nested function) performed on a field.
Here's a useful way to bring back random sorts from a table (we'll just bring back 5 rows by means of the LIMIT keyword):
Code:
SELECT location, amount, order_id | |
FROM ORDERS | |
ORDER BY rand() limit 5; |
There's something you should be aware of when making performing sorts :- null values may produce different results to what you expect. I will dedicate a later entry in my blog to dealing with NULLs.
