How to Get the 2nd the 2nd Record AND DISPLAY IN SINGLE ROW ?

[heri]

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 ???
---------------------------------------------------------------------------*-----

This query gets the Max and Min Basic Salary on a certain Date Range.
In case there are 5 records of an employee on certain date range how
can I get the record before the Max and would reflect as my OLDBASIC,
if I use TOP2 DESC it will display 2 records. I only need one record
which should be the Basic Salary before the 1st record on a DESC order.

Please add the solution to my 2nd Select Statement which get the
OLDBASIC salary Thanks ...
SELECT TOP 100 PERCENT E.EmployeeNo, E.LastName, E.FirstName,
E.SectionCode, E.Department, E.DateHired, E.Remarks,
(SELECT TOP 1 ([BasicSalary])
FROM empsalaries AS T14
WHERE T14.employeeno = E.employeeno AND startdate BETWEEN @FromDate AND

@ToDate
ORDER BY startdate DESC) AS NEWBASIC,
******************************* BELOW I SHOULD ALWAYS GET THE BASIC
SALARY PRIOR TO THE 1ST RECORD AND IN A SINGLE ROW ???
(SELECT TOP 1 ([BasicSalary]) (
FROM empsalaries AS T14
WHERE T14.employeeno = E.employeeno AND startdate BETWEEN @FromDate AND

@ToDate
ORDER BY startdate ASC) AS OLDBASIC
FROM dbo.Employees E
WHERE CONVERT(VARCHAR(10),E.DateHired, 101) BETWEEN @FromDate AND
@ToDate
ORDER BY E.LastName

[Erland Sommarskog]

heri (he************@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Madhivanan]

or

Select min(col) from
(
select top N col from table Order by col DESC
) T

Madhivanan