Monday, September 2, 2019

Query Question in SQL

1. Find nth salary form Employee:

TO FIND NTH HIGHEST SALARY USING CTE
  1. SELECT*FROM  [DBO].[EMPLOYEE] ORDER BY SALARY DESC  
  2. GO  
  3.   
  4. WITH RESULT AS  
  5. (  
  6.     SELECT SALARY,  
  7.            DENSE_RANK() OVER (ORDER BY SALARY DESC) AS DENSERANK  
  8.     FROM EMPLOYEE  
  9. )  
  10. SELECT TOP 1 SALARY  
  11. FROM RESULT  
  12. WHERE DENSERANK = 3  

duplicate records

SELECT username, email, COUNT(*)
FROM users
GROUP BY username, email
HAVING COUNT(*) > 1


If you want to delete the duplicates, here's a much simpler way to do it than having to find even/odd rows into a triple sub-select:
SELECT id, name, email 
FROM users u, users u2
WHERE u.name = u2.name AND u.email = u2.email AND u.id > u2.id
And so to delete:
DELETE FROM users
WHERE id IN (
    SELECT id/*, name, email*/
    FROM users u, users u2
    WHERE u.name = u2.name AND u.email = u2.email AND u.id > u2.id
)
If you want to delete duplicate record with contains one record.
with TEST as(
 select ProductId,city, ROW_NUMBER()  OVER(PARTITION BY city ORDER BY city) AS T from PRODUCTDETAILS
)
SELECT * FROM TEST WHERE T>1

How to use SQL pagination using LIMIT and OFFSET

  How to extract just a portion of a larger result set from a SQL SELECT. LIMIT n is an alternative syntax to the FETCH FIRST n ROWS ONLY. T...