Selecting next to maximum record 1

[cousinconley]

Heres the scenario...I have a table with effective dated records. This means employee 123 can have multiple records all identified by a date.

Example: employee 123 gets a pay raise on 01-29-2006. Employee 123 then gets another raise 08-30-2006. Later employee 123 gets yet another raise 05-27-2007.

Question: how would you via SQL statement select the next to maximum dated record (08-30-2006)?

 

[bborissov]

SELECT TOP 1 *
FROM (SELECT TOP 2 * FROM YourTable
             WHERE Empl = 123
             ORDER BY DateField DESC) Tbl1
ORDER BY DateField

not tested

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[cousinconley]

My appologies..after I posted this message it came to me:

SELECT A.* FROM PS_JOB A WHERE A.EMPLID = '123'
AND A.EFFDT = (SELECT MAX(B.EFFDT) FROM PS_JOB B WHERE B.EMPLID = A.EMPLID AND B.EFFDT <>
(SELECT MAX(C.EFFDT) FROM PS_JOB C WHERE C.EMPLID = A.EMPLID))

 

[cousinconley]

Thanks bborissov, yours looks more strait forward than mine.

 

[ESquared]

SELECT TOP 1 *
FROM EmpPayHistory
WHERE
   EmpID = 123
   AND EffectiveDate < (
      SELECT Max(EffectiveDate)
      FROM EmpPayHistory
      WHERE EmpID = 123
   )
ORDER BY EffectiveDate DESC

Borislav's code will be more efficient than mine since he's hitting the table only once. However, if you need to do this query on a bunch of employees at once, you'll need a new method and this query is one step toward that. It might look something like this:

SELECT H.*
FROM
   EmpPayHistory H
   INNER JOIN (
      SELECT
         EmpID,
         Date2 = Max(EffectiveDate)
      FROM
         EmpPayHistory H2
         INNER JOIN (
            SELECT 
               EmpID,
               MaxDate = Max(EffectiveDate)
            FROM EmpPayHistory
            GROUP By EmpID
         ) X ON H2.EmpID = X.EmpID AND H2.EffectiveDate < X.EffectiveDate
   ) X2 ON H.EmpID = X2.EmpID AND H.EffectiveDate = X2.EffectiveDate

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

[sub]The best part about anything that has cheese is the cheese.[/sub][/color]