This guide will delve into the syntax of the ORDER BY clause, exploring its applications, limitations, and practical examples to empower SQL practitioners with the skills to organize and present data efficiently.
Whether you are a beginner or an experienced SQL user, mastering the art of ordering results is a crucial step in optimizing the presentation of database query output.
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
In conclusion, mastering the art of ordering results in SQL queries emerges as a crucial skill for anyone working with databases. The SQL ORDER BY clause, explored in this guide, serves as a powerful instrument to customize the presentation of data, enabling users to make informed decisions and glean insights from their datasets. By delving into the nuances of ascending and descending sorting, understanding the syntax, and applying these concepts through practical examples, users can wield the ORDER BY clause effectively.
Whether organizing a list of products, sorting customer data, or refining search results, the ability to control the sequence of output ensures that the presented information is not only accurate but also easily digestible.
As you start your SQL journey, consider the ORDER BY clause as an indispensable tool for transforming raw data into meaningful and actionable insights.
We also 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.
Other SQL Articles you may like
No comments:
Post a Comment