group by syntax help

[bopritchard]

table1
ID CompanyID Date
1 123 1/1/2003
2 123 1/1/2004
3 456 2/1/2003
4 123 1/1/2005
5 456 2/1/2004

My result set should look like this
CompanyID Date PreviousDate
123 1/1/2005 1/1/2004
456 2/1/2004 2/1/2003

Logic is this:
only the MOST recent(by Date) record PER companyID should be shown...and the date from the 2nd MOST recent(by Date) record should be appened to the record as PreviousDate column

 

[Glenn9999]

Taking a shot at this (untested):

Anyway, to get reach record set, we need to do a self-join with some dictated parameters on each of the fields.

SELECT T1.COMPANYID, T1.CURRENTDATE, T2.PREVIOUSDATE
(SELECT COMPANYID, 
        MAX(DATE) AS CURRENTDATE
  FROM TABLE1
 GROUP BY CompanyID) T1
INNER JOIN
(SELECT COMPANYID, 
        MAX(DATE) AS PREVIOUSDATE
  FROM TABLE1
 WHERE DATE <
   (SELECT  MAX(DATE) 
     FROM TABLE1
     GROUP BY COMPANYID)
  GROUP BY COMPANYID) T2
ON T1.COMPANYID = T2.COMPANYID
ORDER BY T1.COMPANYID

That's the first shot I'd take at solving this problem if it were in front of me.

 

[PHV]

SELECT CompanyID, MAX(Date) AS Date, (SELECT MAX(Date) FROM table1 B WHERE B.CompanyID=A.CompanyID AND B.Date<(SELECT MAX(Date) FROM table1 C WHERE C.CompanyID=B.CompanyID)) AS PreviousDate
FROM table1 A
GROUP BY CompanyID

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244