Pragmatic Design And Coding By Manish Singh

Sunday, June 07, 2009

Fun with SQL Query

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

SQL

Sunday, June 07, 2009 6:07:50 AM (GMT Standard Time, UTC+00:00)

Comments [1] Trackback

Monday, November 14, 2011 10:48:32 PM (GMT Standard Time, UTC+00:00)

I must say that this script will be useful in some situations

professional resumes

Name
E-mail
Home page

	Remember Me
Comment (Some html is allowed: `a@href@title, strike`) where the @ means "attribute." For example, you can use <a href="" title=""> or <blockquote cite="Scott">.
Enter the code shown (prevents robots):
Live Comment Preview

Find Nth Max or Min Record

Row Number

Running Total

Find Duplicates

Let SQL Generate SQL

Find N^th Max or Min Record