Display Top X Records For Each Group

An Utter Access reader posted a question about taking data like this:

METER_ID, READ_DATE, METER_READ
Meter_1, 2/3/2009, 4323
Meter_2, 2/4/2009, 4194
Meter_3, 2/3/2009, 4274
Meter_4, 2/4/2009, 4596
Meter_1, 3/9/2009, 4843
Meter_2, 4/2/2009, 4913
Meter_3, 3/11/2009, 5012
Meter_4, 3/12/2009, 4673
Meter_1, 5/6/2009, 5456
Meter_2, 5/7/2009, 5681
Meter_3, 5/7/2009, 5231
Meter_4, 5/7/2009, 4984
            

and outputting to this format where the two most recent meter readings for each meter looks like this:

METER_ID, DATE_1, READ_1, DATE_2, READ_2
Meter_1, 3/9/2009, 4843, 5/6/2009, 5456
Meter_2, 4/2/2009, 4913, 5/7/2009, 5681
Meter_3, 3/11/2009, 5012, 5/7/2009, 5231
Meter_4, 3/12/2009, 4673, 5/7/2009, 4984
            

Here is one way to use Jet SQL to output the desired info. First make a query that pulls all the records from the Meter table and assigns a Relevance value based on the READ_DATE for each unique meter. The most recent reading for a particular meter gets a Relevance value of 1, the next most recent reading for the meter gets a relevance of 2, etc.

SELECT 
  m.METER_ID
, m.READ_DATE
, m.METER_READ
, (SELECT COUNT(*) FROM Meter WHERE METER_ID = m.METER_ID AND READ_DATE >= m.READ_DATE) As Relevance
FROM Meter m;
            

Next filter the query to include only those with a value of 1 or 2. The result is the last two readings for each meter.

SELECT 
  m.METER_ID
, m.READ_DATE
, m.METER_READ
FROM Meter m 
WHERE (SELECT COUNT(*) FROM Meter WHERE METER_ID = m.METER_ID AND READ_DATE >= m.READ_DATE) < 3 
ORDER BY 1, 2 DESC;
            

But to join the two rows into one for each meter I used two queries, one for each relevance value and I’ll join them together based on the meter ID:

SELECT * FROM 
  ( SELECT 
      m.METER_ID
    , m.READ_DATE As READ_DATE_1
    , m.METER_READ As METER_READ_1
    FROM Meter m 
    WHERE (SELECT COUNT(*) FROM Meter WHERE METER_ID = m.METER_ID AND READ_DATE >= m.READ_DATE) = 1 
  ) As t1
  LEFT JOIN
  ( SELECT 
      m2.METER_ID
    , m2.READ_DATE As READ_DATE_2
    , m2.METER_READ As METER_READ_2
    FROM Meter m2 
    WHERE (SELECT COUNT(*) FROM Meter WHERE METER_ID = m2.METER_ID AND READ_DATE >= m2.READ_DATE) = 2 
  ) As t2
  ON t1.METER_ID = t2.METER_ID ;
            

which provides output that looks like this:

t1.METER_ID READ_DATE_1 METER_READ_1      t2.METER_ID READ_DATE_2 METER_READ_2
Meter_1     5/6/2009    5456              Meter_1     3/9/2009    4843
Meter_2     5/7/2009    5681              Meter_2     4/2/2009    4913
Meter_3     5/7/2009    5231              Meter_3     3/11/2009   5012
Meter_4     5/7/2009    4984              Meter_4     3/12/2009   4673