How to Order results in SQL query? ORDER BY Example Tutorial

SQL isn't a standard programming language in the sense that it doesn't require you to write a set of instructions in a specific order. Instead, SQL is a "declarative" language, which means that when you write a SQL query, you state what data you want to get as a result of the query but not how you'll get it.

Operations to order

We'll discuss the execution order of the six most typical actions or parts of a SQL query using examples. Because the database executes query components in a specified sequence, knowing that order is beneficial to the developer. 

It's like following a recipe: you need to know the components and what to do with them, but you also need to know how to complete the chores in the right order. If the database performs operations in a different sequence, the query's speed might suffer significantly.

Setting up Database

Let us set up our database. We will use the same database used in our previous articles. Still, for a quick refresh let me paste the queries to create the database so that the readers would have a hassle-free experience.

CREATE TABLE cricketer
(
  id INT NOT NULL PRIMARY KEY,
  name VARCHAR(10) NOT NULL,
  franchise VARCHAR(11) NOT NULL,
  price INT NOT NULL
);

Now, let's insert a few records in it.

INSERT INTO cricketer(id, name, franchise, price) VALUES
  (11, 'Virat','rcb', 501),

  ( 3, 'Rohit','mi', 201),
  ( 5, 'Bumrah','mi', 401),
  ( 7, 'Jadeja','rcb', 101),
  ( 8, 'Dhoni','mi', 401),
  ( 2, 'Surya','rcb', 51);

The resultant table would look something like the below: 

Let's start with a basic query to get the names of the RCB franchise's players:

Change in order of operations if we add ORDER BY

Assume your IPL supervisor receives a report based on the previous example's query and rejects it because the player names are not in alphabetical order. You need to add an ORDER BY clause to the previous query to solve it:

SELECT name, price FROM cricketer WHERE franchise = 'rcb' ORDER BY name;

This query follows the same steps as the previous one in terms of execution. The ORDER BY phrase is processed at the end, which is the sole difference. The query's final result, as seen above, organizes the items by name.

Using GROUP BY and HAVING clauses

We'll use a query using GROUP BY in this example. Let's say we want to know how many players in each franchise have a price higher than 201, and we want the results in decreasing order by franchise size. In this case, the question is:

SELECT franchise, COUNT(*) FROM cricketer 
WHERE price > 201 GROUP BY franchise ORDER BY COUNT(*) DESC;

The result of the query is as below : 

We'll utilize the HAVING clause in the next example. HAVING isn't as well-known in SQL as the other clauses we've discussed so far. The easiest way to think of HAVING is to compare it to the WHERE clause in GROUP BY. In other words, it's a means to filter or eliminate some of the GROUP BY groupings of records.

Assume we now want to get all of the franchises with an average salary of more than 101, with the exception of the mi franchise. In this case, the inquiry is:

SELECT franchise FROM cricketer 
WHERE franchise <> 'mi' GROUP BY franchise HAVING AVG(price) > 101;

Adding a new operation: JOIN clause

Now, for using the JOIN clause we must have a second table for that. Let's create a second table called a franchise.

CREATE TABLE franchise
(
  id INT NOT NULL PRIMARY KEY,
  fname VARCHAR(101) NOT NULL,
  budget_cr INT NOT NULL
);

Now, let's insert a few data into the table. Use the below queries for the same.

INSERT INTO franchise(id, fname, budget_cr) VALUES
    (1, 'Gujarat Titans', 1001),
    (2, 'Royal challengers banglore', 901),
    (3, 'Mumbai Indians', 9999),
    (4, 'Lucknow super giants', 201);

The resultant table would look like below : 

Now let's use the JOIN clause for our 2 tables.

SELECT * FROM cricketer INNER JOIN franchise ON cricketer.id = franchise.id;

The result would be as below : 

Conclusion

We went through the execution order of SQL queries with examples in this post. We can see that there is an order of execution in these samples, but it varies based on which clauses are contained in the query.