DeptNo | Dname | Loc |

50 | Service | Delhi |

51 | Account | Mumbai |

Max(Salary)

FROM Department_Table

GROUP BY Department_Name

Though the variable PaymentYear has already been defined in the first line of the query, but this is not the correct logical process order. The correct query will be
SELECT Id, Year(PaymentDate) as PaymentYear
FROM Bill_Table
WHERE Year(PaymentDate)>2018;

The order of query goes like this:-

FROM – Choose and join tables to get the raw data

WHERE – First filtering condition

GROUP BY – Aggregates the base data

HAVING – Apply condition on the base data

SELECT – Return the final data

ORDER BY – Sort the final data

LIMIT – Apply a limit to the returned data

FROM – Choose and join tables to get the raw data

WHERE – First filtering condition

GROUP BY – Aggregates the base data

HAVING – Apply condition on the base data

SELECT – Return the final data

ORDER BY – Sort the final data

LIMIT – Apply a limit to the returned data

It assigns a unique id to each row returned from the query ,even if the ids are the same.

Sample query:- SELECT emp.*, row_number() over (order by salary DESC) Row_Number from Employee emp;

Even when the salary is the same for Bhargav and Chirag, they have a different Row_Number, this means that the function row_number just gives a number to every row

Sample query:- SELECT emp.*, row_number() over (order by salary DESC) Row_Number from Employee emp;

Employee Name | Salary | Row_Number |

Amit | 7000 | 1 |

Bhargav | 6000 | 2 |

Chirag | 6000 | 3 |

Dinesh | 5000 | 4 |

Esha | 3000 | 5 |

Farhan | 3000 | 6 |

Even when the salary is the same for Bhargav and Chirag, they have a different Row_Number, this means that the function row_number just gives a number to every row

The RANK() function is used to give a rank and not a row number to the data set. The basic difference between RANK() and ROW_NUMBER is that Rank will give equal number/rank to the data points with the same value. In the above case, RANK() will give a value of 2 to both Bhargav and Chirag and thus will rank Dinesh as 4. Similarly, it will give rank 5 to both Esha and Farhan.

SELECT emp.*,
RANK() over (order by salary DESC) Ranking
from Employee emp;

Employee Name | Salary | Row_Number |

Amit | 7000 | 1 |

Bhargav | 6000 | 2 |

Chirag | 6000 | 2 |

Dinesh | 5000 | 4 |

Esha | 3000 | 5 |

Farhan | 3000 | 6 |

*/?>

*/?>

*/?>

*/?>