Group on Max Date one previous to max date

[kayek]

I have a table called tblAssetvalue. Each month my users input the value of their house forsale. In my report I need to show the most resent value and the one previous. I can get the most resent easily by grouping on max date but how can I get the one previous?

 

[rac2]

The second most recent one is the one with the max date less than the most recent one.

SELECT house_id, MAX(date_of_price) AS "RecentDate"
FROM tblAssetvalue
GROUP BY house_id

would be the most recent date. Save that query and call it HouseLatestDate.

SELECT a.house_id, MAX(a.date_of_price) AS "PriorDate"
FROM tblAssetvalue a
JOIN HouseLatestDate b ON b.house_id = a.house_id
WHERE a.date_of_price < b.RecentDate
GROUP BY a.house_id

would be the previous date for each house.

Note that in HouseLatestDate, there is exactly one date for each house. Thus the maximum date also that same date. So you can

SELECT a.house_id,
       MAX(a.date_of_price) AS "PriorDate",
       MAX(b.RecentDate) AS "LatestDate"
FROM tblAssetvalue a
JOIN HouseLatestDate b ON b.house_id = a.house_id
WHERE a.date_of_price < 
GROUP BY a.house_id

Or you could group by b.RecentDate also. Again because there is only one value of b.RecentDate for each house the GROUP BY will yield one line per house and RecentDate combination.