Sorting database results

Databases offer a lot of flexibility when dealing with data. By spending some time upfront on the database design, it will save you plenty of headaches later on. Since the object of this to tutorial is not to elaborate on the advantages of using a database, I will skip to its limits.

The operations you can run on a database are always based on logic and numbers. So for example, when you run a store and sell T-Shirts, and you sort by size, “M” will always be sorted before “S”, even though it is not correct from a humanistic point of view.

Assuming we have a table called “Products” in the database, here is a set of data that you would typically deal with and a solution for sorting the data in a more useful way using the MySQL database.

Name    | Size 

T-Shirt | L 

T-Shirt | M 

T-Shirt | S 

T-Shirt | XL 

T-Shirt | XS

And here is how you typically retrieve records:

SELECT Name, Size FROM Products ORDER BY Size

This query returns all products, however, not sorted “XS, S, M, L, XL” but in “L, M, S, XL, XS”.

The solution to this problem is a simple CASE (see: Control Flow Functions):

SELECT Name, Size, 

CASE Size 

WHEN XS THEN 1 

WHEN S THEN 2 

WHEN M THEN 3 

WHEN L THEN 4 

WHEN XL THEN 5 

END 

AS Sort_Order 

FROM Products 

ORDER BY Sort_Order ASC

This returns the products in order of their sizes. Use ASC (ascending) or DESC (descening) to change the order from smallest size to largest size or vice-versa.

That’s it, enjoy.

Did you like this post? Get monthly summary of our new tutorials, posts and tips to your inbox!

No Responses

Post Your Comment

Your email address will not be published.