|
Archives
|
| | Sun | Mon | Tue | Wed | Thu | Fri | Sat |
|---|
| 27 | 28 | 29 | 30 | 1 | 2 | 3 | | 4 | 5 | 6 | 7 | 8 | 9 | 10 | | 11 | 12 | 13 | 14 | 15 | 16 | 17 | | 18 | 19 | 20 | 21 | 22 | 23 | 24 | | 25 | 26 | 27 | 28 | 29 | 30 | 31 | | 1 | 2 | 3 | 4 | 5 | 6 | 7 |
|
|
Disclaimer
The opinions expressed herein are my own personal opinions and do not represent
my employer's view in any way.
Copyright 2010 Manish Kumar Singh
|
|
 Sunday, June 07, 2009
Fun with SQL Query
Find Nth Max or Min RecordThis 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 NumberThis 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 TotalThis 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)  Trackback
|
|
|