) Code language: SQL (Structured Query Language) ( sql ) INSERT INTOmarks( name, class, marks) VALUES It is very useful when we want to find the ranks such as third highest, second last etc.We will create a table to store the names and marks of students from different divisions. It gives the same rank to more than one row in case of ties. RANK() is a window function that sequentially ranks the rows based on the values of a specified column. RANK() OVER (ORDER BY SUM(marks) ASC) as rank Example #5įind the first name and last name of the student who scored third lowest marks in mathematics. So, in order to use the results of any window function we can make use of CTE or WITH expressions that let us create temporary tables. We cannot directly use the rank column in the WHERE clause because rank is part of the SELECT statement and WHERE filtering happens before selection. The above example teaches us how to use rank or any window function in the WHERE clause. GROUP BY student_id,first_name,last_name) RANK() OVER (ORDER BY SUM(marks) DESC) as rank Example #3įind the details of the last three students who have scored lowest marks overall.įind the first name and last name of the student who scored third highest marks.ĪS (SELECT student_id,first_name,last_name, In this case, we can see that the student has been clearly identified along with his or her rank in the list. GROUP BY student_id,first_name,last_name Let’s prepare a merit list of students consisting of their student id, names and total marks obtained by them. But it can be very confusing to some students as it does not have the first and last names of the students. Prepare a merit list of students based on the total marks obtained by them.Ībove example provides a merit list based on the total marks obtained by the students. In this case, partition by clause divides the rows in the result set into two parts, one for maths and other for science and makes a more sensible ranking based on the subjects. RANK() OVER (PARTITION BY subject ORDER BY marks DESC) merit_list In this case, we can see that when we are ranking without partition function, the entire rows in the result set is considered as a single group and hence it does not yield a meaningful result. RANK() OVER (ORDER BY marks DESC) merit_list SQL query to illustrate ranking of students based on marks obtained by them. The data in the ‘students’ table after insertion looks something like this: Let’s insert some values in the table to populate the dataset using the INSERT statements. The ‘students’ table can be created in the following manner. The table will contain student details such as their student id, first name, last name, marks, subject, etc. In order to illustrate the functions and applications of the RANK() function in SQL, let’s create an imaginary ‘students’ table. RANK() function is used as part of the SELECT statement. Mention the column name based on the values of which you want to order the partition set.ģ. order_by_clause: order by clause is used to order the values in the partition set.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |