MS Access SQL Get Latest Records From Each Categor

问题:

I have an Access database that is getting new data on a daily basis. Each set of data contains several categories and each category may have 0 or many records.

Every day I will append this data to the database with an additional column for today's date. Therefore, same record may exist across multiple days.

For example, the columns I have may include:

• Category
• Detail1
• Detail2
• ...
• Report_Date

What I need to get from this database is the latest details under each category.

Some of the problems I face are

1. Number of records are different in each category and in each day. I can't just get a fixed number of records from each category.
2. Not all categories have records on the same day, so I can't fix a date as the latest date to retrieve records from each category.

Is there any good way I can do this by SQL besides UNION every single category up?

Thanks in advance for the help.

回答1:

I just figured it out. I can use a subquery to do this.

Here it goes.

SELECT * 
FROM my_table AS T1
WHERE T1.report_date = (SELECT MAX(report_date) 
                        FROM my_table AS T2  
                        WHERE T1.category = T2.category)