Search code examples
sqlsql-servert-sqlsql-server-2000

Getting newest 2 values per group of rows SQL Server 2000

So I have combed through multiple pages of stackoverflow questions and it seems like I keep getting close but dont get the exact results I need...so I have succumbed to submitting this question.

To start off this is on a SQL Server 2000 server...which sucks, I know.

I have 2 tables linking together in a sql query that is overall fairly basic. Sample data is listed below: Current Results

The query I use to get that data is:

SELECT DISTINCT
AH.locationid as locationid
,AH.adate as date
,Min( CASE WHEN Asum.code = '201' THEN Amount END ) AS attrib1
,Min( CASE WHEN Asum.code = '204' THEN Amount END ) AS attrib2
,Min( CASE WHEN Asum.code = '206' THEN Amount END ) AS attrib3
,Min( CASE WHEN Asum.code = '212' THEN Amount END ) AS attrib4
,Min( CASE WHEN Asum.code = '215' THEN Amount END ) AS attrib5
,Min( CASE WHEN Asum.code = '218' THEN Amount END ) AS attrib6
,Min( CASE WHEN Asum.code = '221' THEN Amount END ) AS attrib7
,Min( CASE WHEN Asum.code = '231' THEN Amount END ) AS attrib8
,AH.checkid as checktype
FROM [asum] ASum

INNER JOIN [Ahead] AH
ON  Asum.AID = AH.AID

WHERE AH.code IN ('212','201','206','204','215','218','221','231')

AND AH.checkid <> 'c'
AND AH.checkid <> 'b'

GROUP BY 
 AH.code
,ASum.amount
,AH.adate
,AH.checkid

ORDER BY 
 AH.locationid
,AH.adate
,attrib1
,attrib2
,attrib3
,attrib4
,attrib5
,attrib6
,attrib7
,attrib8
,AH.checkid'

So what I am trying to do is return the newest two dates for every location and all of the attrib for those newest two dates.

So I am essentially trying to get something that looks like this: Wanted Results

The closest I have gotten is doing adding the below inner join code to the query and that gets me just the top date...the problem I face is how to get two max dates from this subquery that I innerjoin too.

  INNER JOIN (SELECT 
                locationid,
                MAX(adate) as newest

             FROM   ahead

             GROUP BY locationid) t1
 ON t1.locationid = AH.locationid
 AND t1.newest = AH.adate

So with that inner join I just get 1 of each location id instead of two of each location id like I need. The rough part is that this is on a SQL Server 2000 database...UGH.

Thanks so much in advance for any tips/advice!

Solution

  • I can't test it, because I don't have SQL Server 2000, but I think this might work:

     INNER JOIN (SELECT a1.locationid,
                    MAX(a1.adate) as newest,
                    MAX(a2.adate) as second_newest
               FROM ahead a1
               JOIN ahead a2
                 ON a2.locationid = a1.locationid
                AND a2.adate < a1.adate
              GROUP BY a1.locationid) t1
ON t1.locationid = AH.locationid
AND (t1.newest = AH.adate OR t1.second_newest = AH.adate)

    EDIT: Looking at John Bollinger's comment, I should add that I only expect this to work if the (locationid, adate) combination is unique.