Friday, January 12, 2024

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

Ordering results in SQL is a fundamental aspect of retrieving and presenting data in a meaningful way. When querying a database, the order in which the results are displayed can significantly impact the user's understanding of the information. The SQL ORDER BY clause provides a powerful mechanism to sort query results based on one or more columns in ascending or descending order. Whether you are working with a small dataset or a large database, understanding how to order results allows you to tailor the presentation of information to meet specific requirements. 

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: 

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




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


How to use ORDER BY Clause in SQL



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;


ORDER BY EXAMPLE




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 : 

ORDER BY Example with GROUP BY and HAVING


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;



ordering example in SQL




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 : 

how to order rows with JOIN in SQL



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 : 

How to print SQL result in increasing or decreasing order




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