All language subtitles for 4. RANK and DENSERANK

Now there are two more functions that are similar to rule number.

The first one is rank.

So I will tell you the difference from this example.

So just like in the raw number here, we have three columns customer, store and order.

In fact, we have the same data.

Now, if I apply rank instead of row number, it will again create partition, sort my data and provide

row number or rank in a new column.

So just like row number here.

Also I will get one, two, three.

For a store.

A with order.

Six, three, two.

But for a store B.

I will get 1 to 2 for.

So if there is a tie.

I am providing these raw numbers on the basis of number of orders.

So if there is a tie on orders, rank will provide the same ring to all the tied values.

So in row number.

We saw that.

It provided rule number of two to C three and row number of three to see six because C three was appearing

first in our table.

So that's why it provided two to see three and three to see six.

Even though the number of orders for both of these customers were same.

But in the rank we will get the same number of ranking to both the customers.

One more important point is after ranking this customer so C three and see six are both getting row

number or rank as two.

After this it will directly jump to four.

So to the fourth or the last customer, it will give a rank of four.

It will not give three.

It will give four because there are two customers on rank two.

That's why it is giving four to the next customer.

If there are three customers on rank, two then to the next customer it will provide five.

So when you are using rank, it will escape some ranks in between.

The formula and the syntax is almost same.

Just instead of raw number, you have to use rank.

Now another variation of rule number and rank is dense rank.

And dancing.

The only difference is here in rank.

We were skipping number three then.

Rank will never skip any rank.

So even if there are three customer on rank two to the fifth customer, it will provide rank three.

So there is no skipping of rank and dense rank and there is a skipping of rang and rang function.

Intense drink and drink.

There are same ranking for tide values.

In raw number.

All the rows within the partition will have the unique value.

So remember these three functions.

The difference between these three are asked frequently in many interview questions.

So once again, I'll repeat in row number, all the rows will be unique within a partition.

In rank and dancing.

There can be same ranking on tide values.

And there is no skipping of rank and dense rank.

As the name suggests, densely ranked.

And there will be a skipping of ranks in rank function if there is a tie.

Again for dense ring.

Also, the syntax is almost same.

Just instead of rule number or rank, you have to use dense rank.

Now let's apply this in our data.

So in our last lecture, we created row and variable.

Now let's create rank and then rank variable in the same table.

So I will just copy paste this.

For ring, we will rate the ring.

And here also I will rate.

I will name this column as rank number.

And similarly for dance.

Ring will write dance.

Ranke and I will save this column as drink.

Now let's run this.

There is an additional comma will remove it and then run it again.

So you can see that if there is no tie.

We are getting the same values.

But here in this row, we have a tie.

So for customer 80 705 and 615, both have number of orders as eight in state of Alabama.

So in case of roll number, we are getting 1 to 3 in case of rank.

I'm getting 1 to 2 and in case of dense ring, also I'm getting 1 to 2.

But for the next customer, we are getting one, two, three, four in the roll number.

We are getting one, two, two, four in rank.

And because we are skipping ranked number three.

But in the dense ring, we are getting one, 2 to 3.

So we are not skipping any rank in dense ring.

You can also look at this for customers from California.

So you can see that there are multiple customers with number of orders as well.

So there are four customers with number of orders as well in California.

For these customers, we are getting three, three, three, three in rank and then seven.

But in dancing we are getting to 2 to 2 and then three.

We are getting to hear instead of three because there are two people in rank one.

So we are skipping number to four, five, six and so on.

So this is the difference between row number, rank and dense rank.