cte in sql

CTE are commonly used for storing data temporarily in SQL Server.

delete duplicate records in table using cte :

WITH EmployeesCTE AS
(
   SELECT *, ROW_NUMBER()OVER(PARTITION BY ID ORDER BY ID) AS RowNumber
   FROM Employees
)
DELETE FROM EmployeesCTE WHERE RowNumber > 1



N th highest salary

using Max() function:

Select Max(Salary) from Employees

use a sub query along with Max() function :

Select Max(Salary) from Employees where Salary < (Select Max(Salary) from Employees)

To find nth highest salary using Sub-Query :

SELECT TOP 1 SALARY
FROM (
      SELECT DISTINCT TOP N SALARY
      FROM EMPLOYEES
      ORDER BY SALARY DESC
      ) RESULT
ORDER BY SALARY

To find nth highest salary using CTE :

WITH RESULT AS
(
    SELECT SALARY,
           DENSE_RANK() OVER (ORDER BY SALARY DESC) AS DENSERANK
    FROM EMPLOYEES
)
SELECT TOP 1 SALARY
FROM RESULT
WHERE DENSERANK = N


To find 2nd highest salary we can use any of the above queries. Simple replace N with 2.

Similarly, to find 3rd highest salary, simple replace N with 3.

WITH RESULT AS
(
    SELECT SALARY,
           ROW_NUMBER() OVER (ORDER BY SALARY DESC) AS ROWNUMBER
    FROM EMPLOYEES
)
SELECT SALARY
FROM RESULT
WHERE ROWNUMBER = 3

splitting table column value in sqlserver

Declare @delimiter VARCHAR(50)
Set @delimiter=','
;WITH Cte AS
(
SELECT
[Student ID],
[Student Name],
-- Replace the delimiter to the opeing and closing tag
--to make it an xml document
CAST('<M>' + REPLACE([Code], @delimiter , '</M><M>') + '</M>' AS XML) AS [Code]
FROM [Student]
)
Select
[Student ID],
[Student Name],
--Query this xml document via xquery to split rows
Split.a.value('.', 'VARCHAR(MAX)') AS [Code]
FROM Cte
CROSS APPLY [Code].nodes('/M')Split(a)


--------------------------------------------------------------------------
 ;with cte as
(
select row_number()over(order by (select 0)) row,* from [ufn_Split] (@Typeid,',')
),
cte1 as
(
select row_number()over(order by (select 0)) row,* from [ufn_Split] (@Amount,',')
)

insert into dbo.SA_FeeStructure(Typeid,Amount, courseid,remarks,Status,Date,Duration)
select convert(int,a.[value]),b.[value],@courseid,@remarks,@Status,getdate(),@Duration from cte a,cte1 b where a.row=b.row