WITH result AS
(
SELECT
salary
, dense_rank over (ORDER BY salary DESC) AS denserank
)
SELECT *
FROM result
WHERE result.denserank = 3
####2###nth highest #####
SELECT TOP 1 salary
FROM (SELECT DISTINCT TOP 2 salary
ORDER BY salary DESC)
ORDER BY salary
###3###ASC & DESC #####
SELECT *
FROM customers
ORDER BY nameid ASC,country DESC
###4###get organization hierarchy #####
DECLARE @ID INT;
SET @ID =7;
WITH employeecte
AS (SELECT empid,
empname,
magid
FROM employees
WHERE empid = @ID
UNION ALL
SELECT employees.empid,
employees.empname,
employees.magid
FROM employees
JOIN employeecte
ON employee.empid = employeecte.magid)
SELECT E1.emplyee,
Isnull(E2.empname, 'NoBoss') AS ManagerName
FROM employeecte E1
LEFT JOIN employeecte E2
ON E1.managerid = E2.employid
###5###rows cantain only numerical data #####
SELECT value
FROM testtable
WHERE Isnumeric (value) = 1
###6###Employees hired in last months #####
SELECT *
FROM emploees
WHERE Datadiff(month,hiredate,Getdate()) berween 1
AND 3
ORDER BY hiredata DESC
###7###employee hired in first half of the month####
SELECT *
FROM employee e
WHERE To_number(To_char(e.datehired, 'DD')) <= 15
###8###department wtih highest number of employee####
SELECT TOP 1
departmentname
FROM employee
JOIN department
ON employees.departmentid = depaermtnets departmentid
GROUP BY departmentname
ORDER BY count (*) DESC
###9.1###calculate percentage####
SELECT grade,
Count(*) * 100 / Sum(Count(*))
OVER ()
FROM mytable
GROUP BY grade
###9.2###calculate percentage####
SELECT grade,
Count(*) * 100 / (SELECT Count(*)
FROM mytable)
FROM mytable
GROUP BY grade
###10###delete duplicate rows####
WITH employeecte
AS (SELECT *,
Row_number()
OVER (
partition BY id
ORDER BY id) AS RowNumber
FROM employees)
DELETE FROM employeescte
WHERE rownumber > 1
###11###datediff example####
SELECT Start_Date, End_Date FROM (SELECT Start_Date FROM Projects WHERE Start_Date NOT IN (SELECT End_Date FROM Projects)) a, (SELECT End_Date FROM Projects WHERE End_Date NOT IN (SELECT Start_Date FROM Projects)) b WHERE Start_Date < End_Date GROUP BY Start_Date ORDER BY DATEDIFF(End_Date, Start_Date), Start_Date
