Simple hint, how to select a number random items from a MySQL database:
SELECT *
FROM your_table
ORDER BY rand()
LIMIT 2;
The code above, selects all the fields from a database table named “your_table” orders the results randomically and gets the first two. Change two for your favourite number.
Want to see an example of this working? Just look to your right! On the sidebar, there is a section named “Recommended Reading”. This is a section with books I like and recommend. I keep a list of books on a database and I pick two six at random with a query like this, each time a page is loaded.
Maybe you did knew this command already, or at least you heard about the ORDER BY command, but do you exactly know how it works and how further you can go with it?
I’ll show you some nice things in this article.
[All the code on this page works in MySQL, SQL syntax may vary in other Database Management Systems, but the same ideas apply to all]
How does ORDER BY works
The ORDER BY statement on a SQL query atributes an indexing number on each entry of the table.
When you do a
SELECT *
FROM recommendations
ORDER BY price;
An indexing value will be calculated for each entry, based on the expression after the ORDER BY statement. The price will work as variable. The value of the registry will be used as indexing value for it, then the whole list will be sorted based on it. This is how it works.
So, if you use a custom method to set the indexing value you can have your own sorting system. For instance, see this query
SELECT *
FROM recommendations
ORDER BY price - discount;
For each entry, the discount will be subtracted from the price, this number will be set as indexing value for the entry, then, the results will be sorted based on it.
In practice the indexing value will be the final value to be paid, lowest prices first. You can reverse this using descending order.
SELECT *
FROM recommendations
ORDER BY price - discount DESC;
Order By using functions
MySQL comes with a lot of predefined funcions (and you can create funcions of your own, but this will not be covered on this post).
One of theese funcions is rand(). The rand() function returns a random number every time it is called.
SELECT *
FROM recommendations
ORDER BY rand();
When used in a query like the one above, the rand() funcion will be called for each entry and a random number will be set as indexing value for them. The table will, then, be sorted based on theese numbers. The result would by, obviously, that entries will appear in random places.
If you only want to select, say, 2 random entries from the table, you can limit your search using
SELECT *
FROM recommendations
ORDER BY rand()
LIMIT 2;
Boolean searching criteria, one step beyond
[Now, before anything else, I must give credit to Benjamin Wilger for his comment on Dave's blog.]
What if you want to just sort through boolean criteria. For instance, what if you don’t want to sort by item final price, but you do want those who don’t have a discount to come before who have?
SELECT *
FROM recommendations
ORDER BY (discount > 0);
Now, this is tricky, so let’s see it carefully.
The expression (discount > 0) is a logical statement, not an arithmetic one. This means that the answer to that is either true or false instead of a number.
But in databases, everything are numbers on the lowest level. False is actually a zero and true is actually a one.
When you set the result of (discount > 0) as the indexing value, you are actually setting zeroes and ones as sorting criteria and, because zero comes first, the entries in which the expression evaluated as false come first.
If you want the items with discount to come first, you can sort in descending order
SELECT *
FROM recommendations
ORDER BY (discount > 0) DESC;
9 Comments
Very nice tips about “ORDER by”. The last example was new to me, i have never thought about boolean formulas with “ORDER by” queries.
Thanks Andre. I didn’t know that either until some weeks ago :-)
When I first saw it, it really expanded my mind.
Hei! Very good article! The last example was indeed remarkable!
MySQL is full of nice functions, take a look at: http://dev.mysql.com/doc/refman/5.0/en/functions.html
And don’t forget: RTFM
I have a tip too ! It is very common to use GROUP BY queries. However, when we use it MySQL sorts the entries by default, spending some computational time. If you wish to optimize GROUP BY queries, it is interesting to not sort using ORDER BY NULL. See the link that Oda cited.
@Oda: That is indeed a useful link, I updated the article to include it. Thanks!
@AndreYK: Great observation, thanks for sharing it.
Spot on with that last tip - thanks! The MySQl manual is not great and I was despairing of Google until now.
@Andy - :-) I’m glad it has been useful
Guten Tag!
Nice article and thanks for the credit! (thank you, google!) But it’s not worth to give the credit to me since all that is written in the MySQL Manual ;-)
A little hint for the ORDER BY rand() stuff: Keep in mind that MySQL optimizes LIMITed Queries. So if you say LIMIT 2 it will just read two entries from the table unless you do an ORDER BY. If you order by an indexed column the job is simple for MySQL. But if you sort by a value produced by a function like rand(), MySQL is forced to do a full table scan to get all the rows. It then needs to copy the unsorted result to memory (if too big it writes them to hard disc) and sorts them. So if you have a fairly big table (e.g. more than 1000 entries) you might get some performance problems.
Greetings, Benjamin
@Benjamin Wilger -
Hi, Ben. I wanted to drop you a line back when I wrote this post, but I couldn’t find you around. Now, I think credit is due, it was a nice solution when showed it up :-)
Thanks for the additional hint!
Trackbacks