Find Nth Max or Min Record

This query gets the Nth highest salary from the emp table. You can replace the Max function with Min to obtain Nth 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 INNERJOIN 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