Important SQL Queries

Deleting only dupes from a table

DELETE FROM mytable
WHERE EXISTS
( SELECT name1, age1, MIN(id) as cf_prog
FROM mytable TAB02
WHERE ( mytable.name1 = TAB02.name1 ) AND
( mytable.age1 = TAB02.age1 )
GROUP BY name1, age1
HAVING ( count(*) > 1 ) AND
( mytable.id <> cf_prog ) )

Selecting Dupes from a table

select cargo_id, dest_id
from routing t1
where
( select count(*)
from routing t2
where t2.dest_id = t1.dest_id ) > 1


Finding the Nth maximum from a table

select *
from yourTable X
where n-1 =
(select count(*)
from yourTable
where salary > X.salary)
order by salary desc

Selecting the top n rows from a table


select *
from employee X
where n >
(select count(*)
from employee
where dept = X.dept
and salary > X.salary)
order by dept, salary desc