Fun with SQL Query

Manish Kumar Singh — Sun, 07 Jun 2009 06:07:50 GMT

Find N^th Max or Min Record

This query gets the N^th highest salary from the emp table. You can replace the Max function with Min to obtain N^th lowest salary.

SELECT Eno, Ename, Desig, Sal, Mgr, DNO
FROM dbo.Emp AS E1
WHERE (N - 1 =
(SELECT COUNT(DISTINCT Sal) AS DistinctSal
FROM dbo.Emp AS E2 WHERE (Sal > E1.Sal)))>

Row Number

This query generates the row number for the records fetched.

SELECT (SELECT COUNT(*) AS Counter
               FROM dbo.Emp AS e2
               WHERE (e2.Eno <= e.Eno)) AS RowNumber,
            Ename, Desig, Sal
            FROM dbo.Emp AS e
            ORDER BY RowNumber >

Running Total

This query computes the running total on salary for the records in Emp table.

SELECT a.Ename, SUM(b.Sal) AS RunningTotal
FROM dbo.Emp AS a
INNER JOIN dbo.Emp AS b ON a.Ename >= b.Ename
GROUP BY a.Ename>

Find Duplicates

This query finds the duplicate records in Emp table.

SELECT Ename, Desig, Sal, COUNT(*) AS Duplicate
FROM dbo.Emp
GROUP BY Ename, Desig, Sal
HAVING (COUNT(*) > 1)
ORDER BY Duplicate DESC, Ename>

Let SQL Generate SQL

This query emits SQL queries as records fetched.

Select 'Select DName from Dept where Dno=' + CAST(DNO as varchar) from Emp>

Manish Kumar Singh