Count Duplicate Records – Rows

 SELECT YourColumn, COUNT(*) TotalCount
  • FROM YourTable GROUP BY YourColumn HAVING COUNT(*) > 1 ORDER BY COUNT(*) DESC
  • Select columnName From Table_name Group By columnName Having count (*)> 1
  • Here’s a handy query for finding duplicates in a table. Suppose you want to find all email addresses in a table that exist more than once:

     
    SELECT email, 
     COUNT(email) AS NumOccurrences
    FROM users
    GROUP BY email
    HAVING ( COUNT(email) > 1 )
    Find Nth Highest Salary of Employee

    How to get 1st, 2nd, 3rd, 4th, nth topmost salary from an Employee table

    • The following solution is for getting 6th highest salary from Employee table ,SELECT TOP 1 salary FROM ( SELECT DISTINCT TOP 6 salary FROM employee ORDER BY salary DESC) a ORDER BY salary You can change and use it for getting nth highest salary from Employee table as followsSELECT TOP 1 salary FROM ( SELECT DISTINCT TOP n salary FROM employee ORDER BY salary DESC) a ORDER BY salary where n > 1 (n is always greater than one)

    Join Query tutorial

GOOD SQL:
select employee, sum(bonus) from emp_bonus 
group by employee having sum(bonus) > 1000;



Solution to finding the 2nd highest salary in SQL
SELECT MAX(Salary) FROM Employee
WHERE Salary NOT IN (SELECT MAX(Salary) FROM Employee )



N- highest salary from employee table 

SELECT * /*This is the outer query part */
FROM CUSTOMERS Emp1
WHERE (4-1) = ( /* Subquery starts here */
SELECT COUNT(DISTINCT(Emp2.SALARY))
FROM CUSTOMERS Emp2
WHERE Emp2.SALARY > Emp1.SALARY)




Advertisements