Hello All, We are going to start new batch from next week. message/call or mail us for more details.

6 July 2012

Frequently asked sql query



-- Find Duplicate Record ----
select empname, count(empname) as Duplicate from employee
group by empname having (count (empname)>1) order by empname  desc

---- Find Second Highest Salary---
select min(salary) from employee where
salary in(select top 2 salary from employee order by salary desc )

----- Find Duplicate Name ----
SELECT empname
FROM employee
GROUP BY empname
HAVING ( COUNT(empname) = 1 )

--- Find Duplicate Name ---
SELECT empname, COUNT(*) TotalCount
FROM employee
GROUP BY empname
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC


-- Find Duplicate -- 
SELECT empname, COUNT(*) TotalCount
FROM employee
GROUP BY empname


--- Select Duplicate ---
SELECT * FROM dbo.ATTENDANCE WHERE AUTOID NOT IN (SELECT min(AUTOID)
FROM dbo.ATTENDANCE GROUP BY EMPLOYEE_ID,ATTENDANCE_DATE) 


--- Delete Duplicate --- 
DELETE FROM dbo.ATTENDANCE WHERE AUTOID NOT IN (SELECT MIN(AUTOID)
FROM dbo.ATTENDANCE GROUP BY EMPLOYEE_ID,ATTENDANCE_DATE)

No comments:

Post a Comment