# Notes

Hi Guys, here i m going to explain how you can give rank to the rows on the basis of column's information and by creating Partition. suppose you have a table, like:- M_Emp EmpId EmpName E001 GAGAN E001 GAGAN E001 GAGAN E002 AMAN E002 AMAN E003 NAMAN Now  Execute following query :-     SELECT EmpId, EmpName,     ROW_NUMBER()OVER(Partition by EMPID ORDER by EMPID) RowId       FROM M_Emp Output :- EmpId EmpName RowId E001 GAGAN 1 E001 GAGAN 2 E001 GAGAN 3 E002 AMAN 1 E002 AMAN 2 E003 NAMAN 1 This will results in the ranking of rows on the basis of column information. I hope you understand the logic/syntax behind. basically It is used to perform any rank based operation.

Hi Guys, here i m going to explain how you can use CTE to delete duplicate records. suppose you have a table, like:- M_Emp EmpId EmpName E001 GAGAN E001 GAGAN E001 GAGAN E002 AMAN E002 AMAN E003 NAMAN Now  Execute following query to delete duplicate records ;WITH CteExample(EmpId , RowId) AS (     SELECT EmpId,ROW_NUMBER()OVER(Partition by EMPID ORDER by EMPID) RowId     FROM M_Emp ) DELETE FROM CteExample WHERE RowId > 1 This query will delete all the duplicate records.

CTE stands for Common Table Expression, use Dr. Google for more specifications. According to me CTE means a temp view/table to hold any query result. Its very useful in writing complex recursive queries. click to see .  Here I am demonstrating a small example of CTE. Suppose, You have a table M_Users , see below :- M_Users Username FirstName admin Gagan uAman Aman uNaman Naman now execute following query :- ;WITH ExampleCTE(Username,FirstName) AS (     SELECT Username, firstname from M_Users ) SELECT * FROM ExampleCTE Output : ExampleCTE Username FirstName admin Gagan uAman Aman uNaman Naman Output is quite simple, here you can write multiple queries defining joins and all. depends upon your requirement.