SQL Stuff

####1###nth highest #####3rd
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