|
|
Posted by Erland Sommarskog on 10/02/98 11:45
heri (heri.carandang@acspacific.com) writes:
> Can you please assist me on how to get the 2nd record in case there are
> 3 or more records of an employee, the query below gets the MAX and MIN
> BasicSalary. However, my MIN Basic Salary is wrong because I should get
>
> the Basic Salary Prior to the 1st Record (DESC)in case there are 3 or
> more records and not the last Basic Salary of the Last Record.
>
>
> How to GET the 2nd Row of Record in Case that There are 3 or more
> records IN A SINGLE ROW ???
SELECT TOP 1 val
FROM (SELECT TOP 2 val
FROM tbl
ORDER BY val DESC) AS x
ORDER BY val ASC
Gives you the second highest value of val.
If you want to do this for a set values, this may be more practical:
SELECT s.empid, secondest = MAX(s.salary)
FROM salaries s
JOIN (SELECT empid, maxsalary = MAX(salary)
FROM salaries
GROUP BY empid) AS m ON s.empid = m.empid
WHERE s.salary < m.maxsalary
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|