NTILE(): This is one of the most coolest function provided by T-SQL. This is very useful while grouping a dataset into multiple sets i.e. in case if you want to distribute the result set into more than one tables or destination, this function is useful in accomplishing this task.
DENSE_RANK(): This is almost similar to RANK() function except, it will assign rank continuously i.e. it will always return consecutive integers. It will assign rank to the records as per the condition used in <order by> clause for a partition without any gaps in the ranking values. The ranking values will be same as of RANK() i.e. if the <order by> condition satisfies for more than one record then it will assign the same rank to each record that satisfies the same condition.
RANK (): For each partition, it will assign the rank to the records as per the condition used in <order by> clause. If the <order by> condition satisfies for more than one record then it will assign the same rank to each record that satisfies the same condition.
ROWNUM(): For each partition, it will return the sequence number starting from 1. It is very useful in case you want any functionality similar to identity column on the fly. As IDENTITY() will work only in case of select into statement i.e. when you are inserting records into some table and not in simple select statement.
How if we found some things in life that we want to be done so smoothly, so perfect without any flaw or fracas that something like a wizard or some magic that makes things simpler and fine.
SQL Server comes with some magic words like the above phrase that makes simple the life of a developer or DBA to a great extent. And these magic words are RowNumber(), Rank(), DenseRank() and NTile().
Problem: Show the list of all employees from the employee table having second highest Salary or Nth highest salary in the organization.
Solution: Well we can achieve the result by so many ways but in this post I would like to do it by using co-related queries. Co-related queries are very interesting stuff as it gives you option to process data row wise. But at the same time it has a big drawback. Row wise processing causes bottle neck for the performance of the query.
Let me start with a simple question. Suppose you want to have a bicycle. How do you want it? Getting the bicycle in one piece from one shop or to have each part from different shop and get it assemble by some mechanic. Yah you got the point. Obviously, you want the bicycle in one piece and readymade.
Employment is the biggest buzzword in the market and yet is the most precious dream of every youngster. The young generation coming out of colleges / institutes with lots of ener
gy and enthusiasm to grow up in the industry and to glow up their future in their respective fields and interests, perhaps don’t know about the up and down and the working mechanism of all this. Most of the people are just concern about their size of the pocket at the end of the month, especially in the IT world.
Don’t have an account? Create one, its simple
Recent Comments