Nexus Archive

ORDER BY

The SELECT clause just spits out data based on conditions/filters but does not really care about what order they come out in, here is where ORDER BY comes to play.

The ORDER BY clause is a sorting clause. It is used to sort the output based on conditions/filters. Its very straightforward to implement in queries.

Lets look at a query to understand how ORDER BY works !

Q. Display names and age of all customers sorted by age in ascending manner.

S. We use ORDER BY to sort output data by age.

SELECT name, age FROM customers ORDER BY age ASC;

This outputs the names and age of all the customers sorted by age in an ascending manner.

The ASC clause denotes "Ascending" and DESC clause denotes "Descending" !

Lets now write a query for the same but sorting by age in descending manner :

SELECT name, age FROM customers ORDER BY age DESC;

This sorts the output data in a descending order of age.

🍢 Multi filter sorting

Since in our DB gameShop we currently have distinct ages for customers the above queries work but what if we enter another record where the age is same with another record already stored ?

Both the ages are same so in the output they come one after another. They still are sorted by age but what if we want a second filter like if ages are same sort them by names in alphabetical order (A - Z) ?

We can actually apply multiple filters by separating each filter using , (comma). If the first filter fails then the second filter is applied, if that fails then the third filter and so on. Lets look at the query to do so.

Q. Display the name and age of all customers sorted by age in ascending order, if ages are same sort by name in alphabetical order (A - Z).

S. Query:

SELECT name, age FROM customers ORDER BY age ASC, name ASC;

That's it ! The filter age ASC is applied first and for any case if that fails it moves on to the second filter name ASC. That's the very gist of it !

Now is good time to speak of the general syntax for ORDER BY.

The general syntax for ORDER BY is:

<...previous part of the query...> ORDER BY <filter 1>, <filter 2>, ..., <filter n>;

The filters are specified by column names (by which column the filter must be applied) followed by what the actual filter is ASC or DESC.

🛑 The LIMIT Clause

Since we are already doing ORDER BY, its good to look at the LIMIT Clause as its very simple and used alongside ORDER BY !

The main function of the LIMIT Clause is to limit the number or records shown.

Q. Display the two oldest customers.

S. We can combine ORDER BY and LIMIT to display the two oldest customers.

SELECT * FROM customers ORDER BY age DESC LIMIT 2;

This query selects all the customers and sorts them by age in descending order therefore the oldest is at the top and since we need the 2 oldest, we limit the output by 2 so only the top 2 records are show which are the oldest ones by age.

🍬 Querying and Combining !

Lets wrap this up by going through a bunch of questions and also seeing how to combine Filtering (SELECT), Sorting (ORDER BY) and Limiting (LIMIT) !

All these questions are those which I used for practicing and learning from chatGPT. Well not all but the best ones...😶

Q. Write a query to get all customers sorted by age from youngest to oldest.

S.

SELECT * FROM customers ORDER BY age ASC;

Q. Write a query to show only the name and address, sorted by address in reverse alphabetical order.

S.

SELECT name, address FROM customers ORDER BY address DESC;

Q. Write a query to show name and address of customers sorted first by address in ascending and then by age in descending.

S.

SELECT name, address FROM customers ORDER BY address ASC, age DESC;

Let's Try combining stuff a bit

Q. Show all customers older than 25 sorted by age in descending order.

S. We are combining Filtering (WHERE) and Sorting (ORDER BY) together. Lets see the query !

SELECT * FROM customers WHERE age > 25 ORDER BY age DESC;

We first filter using the WHERE clause age then we sort the out put based on age in descending order.

Q. Show names of top 2 oldest customers.

S. Sorting (ORDER BY) + Limiting (LIMIT)

SELECT name FROM customers ORDER BY age DESC LIMIT 2;

We display only name sort them by age in a descending manner and then limit the output rows by 2. SIMPLE AF !!!

Q. Show name of youngest customer from Liberty City.

S. Lets break it down, Read the question, It says "youngest customer", therefore we need only 1, ONLY 1 RECORD ! We already got the LIMIT part ! And since we need "YOUNGEST" that means we need the lowest age first, and we get it by......doing ORDER BY !

Next is it says "show name" ! We got the selecting part we need just the name column !

Next we see the filter !! Its there -> "from Liberty City", That's the filter.

We got all the pieces together now just to combine !!!

SELECT name FROM customers WHERE address = 'Liberty City' ORDER BY age ASC LIMIT 1;

THAT'S THE QUERY !

We break it down and then join the dots. What we did here was Filtering + Sorting + Limiting !

Lets look at one last question !

Q. Show the youngest two customers who are older than 20 and live in either Vice City or Liberty City.

S. Now this combines all the 3 previous concepts with Logical operations but its not something we cannot do ! Lets do it ! First we break things up by reading the question !

  1. "Show Youngest 2 customers": It says 2 customers means LIMIT 2 easy 2 records we need we limit the output by 2. Next it says "youngest" that means we need to sort by age in ascending order, isn't it ? So we got our sorting part i.e. ORDER BY age ASC !

  2. "who are older than 20": Sure no problem a simple WHEREclause -> WHERE age > 20 !

  3. "and live in either Vice City or Liberty City": This is another filter we will use the same WHERE clause. So the person can either live in Vice City OR Liberty City, and that's an obvious answer so its address = 'Vice City OR address = 'Liberty City'. Now this in itself is one condition and we need to combine it with the previous one also the question clearly says "who are older than 20 AND live either in Vice City or Liberty City" !

We got all the pieces now we just build the query !

SELECT * FROM customers WHERE age > 20 AND (address = 'Vice City' OR address = 'Liberty City') ORDER BY age ASC LIMIT 2;

Looks tricky until the question is broken.

This part may seem like "Over Explanation" but its exactly how I think and break down stuff mentally.

Therefore, this covers ORDER BY !

Follow Aggregate Functions to continue from here.

Browse Full SQL Page.

#SQL on the go