We can apply a similar approach to our previous query: Let’s suppose we’d like to obtain the data of the employee with the second highest salary in the company. Example #4 - List The Second Highest Row of a Result Set After that, we use ORDER BY ranking to order the rows of the report by ranking value. In the main query, we use WHERE ranking <= 5 to filter the rows with the 5 lowest salaries. RANK() OVER (ORDER BY salary ASC) as ranking
In the CTE, we will create a ranking column based on an ascendent order of salary (lowest salary first): We only need to introduce a change in the type of order, i.e. This query is similar to the top 5 query, but we want the last 5 rows. Example #3 - List the Last 5 Rows of a Result Set
Again, we use an ORDER BY clause to show the result set, which is ordered by rank ascending. This lets us obtain only the top 5 rows by ranking value. The RANK() function for the row with the highest salary will return 1, and so on.įinally, in the WHERE of the main query we ask for those rows with a ranking value smaller or equal than 5. The clause OVER (ORDER BY salary DESC) indicates how the RANK() value must be calculated. The subquery in the CTE uses the function RANK() to obtain the position of each row in the ranking. The WITH clause in the previous query creates a CTE called employee_ranking, which is a kind of virtual table that’s consumed in the main query.
This kind of report must be ordered based on a given criteria in our example, the order criteria will again be salary DESC: The next SQL query creates a report with the employee data for the top 5 salaries in the company. If you want to know more about ranking functions in SQL, I recommend our article What Is the RANK() Function in SQL, and How Do You Use It? Example #2 - List The First 5 Rows of a Result Set We need to use an ORDER BY ranking clause at the end of the query to indicate the order on which the result set will be shown. It is a window function that returns each row’s position in the result set, based on the order defined in the OVER clause (1 for the highest salary, 2 for the second-highest, and so on). In the above query, we use the function RANK(). RANK() OVER (ORDER BY salary DESC) as ranking The report will include the position of each employee in the ranking.
In this example query, we will show a list of all employees ordered by salary (highest salary first). Sometimes we need to create a SQL query to show a ranking of rows based on a specific order criteria. Ok, let’s dig into our advanced SQL queries! 25 Advanced SQL Query Examples with Explanations Example #1 - Ranking Rows Based on a Specific Ordering Criteria
#MYSQL QUERIOUS FULL#
Then, if you like what you’re learning, you can buy full access.
#MYSQL QUERIOUS FREE#
Create a free account and try our interactive courses without having to spend any money. The advanced SQL courses will teach you concepts like window functions, recursive queries, and complex SQL reports. The beginner courses cover the foundations of SQL and are a perfect way to review and refresh your basic SQL knowledge. It contains 7 interactive SQL courses with over 850(!) exercises logically arranged to take you from a complete beginner to an advanced SQL user. You can find the most complete set of interactive SQL courses in our SQL from A to Z track. employee_idĮven for persons with SQL expertise, a good online interactive SQL course can be a real help. Only a few examples will be based on other tables in these cases, the tables will be explained along with the example. Many of the examples in this article will be based on the following employee table.
0 kommentar(er)
