SQL Hacks - Day 4

Today, we''ll study about "Dense Rank" in SQL.

Our business problem was that we needed to find rank of each row with a few caveats. Same number should have the same rank and the next number should begin with the next rank.

We can't use "RANK" function because of the following:

Rank function even though gives the same rank to same number it doesn't give a consecutive rank.

Hence, use Dense rank function to give a consecutive numbers.

select score, DENSE_RANK () OVER (ORDER BY score DESC) AS 'rank' from Scores;

Next thing we'll learn about is "PARTITION"

• It is always used inside OVER() clause.

• The partition formed by partition clause are also known as Window.

• This clause works on windows functions only. Like- RANK(), LEAD(), LAG() etc.

• If this clause is omitted in OVER() clause, then whole table is considered as a single partition.

- Source: Geeks for geeks.

What are window functions?

A window function performs an aggregate-like operation on a set of query rows. However, whereas an aggregate operation groups query rows into a single result row, a window function produces a result for each query row:

• The row for which function evaluation occurs is called the current row.

• The query rows related to the current row over which function evaluation occurs comprise the window for the current row.

Source here

I also learnt about "USING" while joining tables. It can be used instead of an ON clause in the JOIN operations that have an explicit join clause.

Also learnt about the syntax of "self joins"

SELECT B.name as Employee FROM Employee A, Employee B WHERE A.id = B.managerId and B.salary>A.salary ORDER BY A.id;

If you see above code, I have used the same table and named it as A,B

Solved 8 problems today, wuhu!