Best SQL Queries

Use TransactionError

SELECT * FROM DuplicateValues

-- SOLUTION 1: DELETE DUPLICATE VALUE 
DELETE FROM DuplicateValues
WHERE ID IN (
SELECT Min(ID)
FROM DuplicateValues
Group By Name
Having Count(Name) > 1)
GO
-- SOLUTION 2 : DELETE DUPLICATE VALUE 
SELECT *
from DuplicateValues A where ID != (select MIN(ID) from DuplicateValues B where  a.Name=b.Name);

-- Solution 1 : Display Comma Seprated List

SELECT E.State, STUFF((SELECT  ',' + City
            FROM StateCityComb EE
            WHERE  EE.State=E.State
            ORDER BY City
        FOR XML PATH('')), 1, 1, '') AS CityList
FROM StateCityComb E
GROUP BY E.State

-- 2 Min Salary
select distinct Salary from DuplicateValues a  where 2 >= (select count(distinct Salary) from DuplicateValues b  where a.Salary >= b.Salary);

-- Find the Difference Between two Dates
DROP TABLE #TEMP
SELECT * INTO #TEMP FROM(
SELECT 1 As EmpID,'Developer' As Position,'2012-10-10' As UpdateDate
UNION 
SELECT 1 As EmpID,'Sr. Developer' As Position,'2013-10-10' As UpdateDate
UNION 
SELECT 1 As EmpID,'Tech Lead' As Position,'2014-12-12' As UpdateDate
UNION 
SELECT 2 As EmpID,'QA' As Position,'2012-10-10' As UpdateDate
UNION 
SELECT 2 As EmpID,'SR.QA' As Position,'2014-10-10' As UpdateDate
)
As TEmp

SELECT A.EmpID,A.Position,A.UpdateDate,DATEDIFF("D",CONVERT(datetime,A.UpdateDate),MIN(CONVERT(datetime,B.UpdateDate))),
CONVERT(VARCHAR(20),(MIN(CONVERT(datetime,B.UpdateDate)) - CONVERT(datetime,A.UpdateDate)),120)
FROM #TEMP A LEFT JOIN #TEMP B
 ON B.EmpID=A.EmpID AND B.UpdateDate > A.UpdateDate
GROUP BY A.EmpID,A.Position,A.UpdateDate

SELECT A.EmpID,
 A.Position,
 A.UpdateDate,
 ( SELECT CASE WHEN ISNULL(DATEDIFF("DAY",CONVERT(datetime,A.UpdateDate),MIN(CONVERT(datetime,B.UpdateDate))),'') IS NULL THEN GetDate()
     ELSE  ISNULL(DATEDIFF("DAY",CONVERT(datetime,A.UpdateDate),MIN(CONVERT(datetime,B.UpdateDate))),'')
     END
 FROM #TEMP B 
 WHERE A.EmpID=B.EmpID AND B.UpdateDate > A.UpdateDate) As DiffColumn
FROM #TEMP A
GROUP BY A.EmpID,A.Position,A.UpdateDate

-- Allocate position to Students of a class based upon their marks, if same marks same position should be applied
select 'A' [class], 80 [marks], 'manoj' stuName
into #TempStudent
UNION
select 'A', 70 [marks],'harish' stuName
UNION
select 'A', 80 [marks],'kanchan' stuName
UNION
select 'A', 90 [marks],'pooja' stuName
UNION
select 'A', 90 [marks],'saurabh' stuName
UNION
select 'A', 50 [marks],'anita' stuName
UNION
select 'B', 60 [marks],'nitin' stuName
UNION
select 'B', 50 [marks],'kamar' stuName
UNION
select 'B', 80 [marks],'dinesh' stuName
UNION
select 'B', 90 [marks],'paras' stuName
UNION
select 'B', 50 [marks],'lalit' stuName
UNION
select 'B', 70 [marks],'hema' stuName
 
select * from #TempStudent

SELECT class,marks,stuName,
ROW_NUMBER() OVER (order By marks Desc) RowNumber,
RANK() over (order by Marks Desc) RankPosition
FROM #TempStudent

--Rank the Position Class wise
SELECT class,marks,stuName,
ROW_NUMBER() OVER (order By marks Desc) RowNumber,
RANK() over (partition by class order by Marks Desc) RankPosition
FROM #TempStudent

-- Student Class Avg , Sum, Max
select class, stuName, marks,
    AVG(marks) over(partition by class) AS [avg],
    SUM(marks) over(partition by class) AS [sum],
    MIN(marks) over(partition by class) AS [max],
    MAX(marks) over(partition by class) AS [min],
    COUNT(marks) over(partition by class) AS [count],
    STDEV(marks) over(partition by class) AS [ST_Dev],
    VAR(marks) over(partition by class) AS [VAR]
from #TempStudent

-- Identify the second highest salary from emp table having salary related data.
;WITH TCTE AS
(
SELECT SALARY,DENSE_RANK() OVER(order by SALARY Desc) RANKSALARY
FROM Customers
)

SELECT *
FROM TCTE A
WHERE A.RANKSALARY=2

-- 
SELECT TOP (1) SALARY FROM
(
    SELECT DISTINCT TOP (2) Salary FROM Customers ORDER BY Salary DESC
) AS Customers ORDER BY Salary

-- SELECT ALL THE EVEN RECORDS
Select * from Product where ProductID % 2 = 0 
-- SELECT ALL THE ODD RECORDS
Select * from Product where ProductID % 2 = 1