How to obtain the most recent record for each category using SQL

Data and Databases
1

I’m dealing with two tables named feature and inspection. I’m looking for assistance in constructing a SQL query that can retrieve the latest inspection record for every type of feature in its respective inspection category.

Here’s how my feature table is set up:

id  | name
----|-----
1   | tree
2   | flower

And below is the structure of my inspection table:

id | feature_id | category    | date_created
---|------------|-------------|-------------
1  | 1          | inspection  | 10/10/2010
2  | 1          | cleaned     | 10/20/2009
3  | 2          | inspection  | 1/1/2008
4  | 1          | inspection  | 1/1/2005

I need the result to reflect the most recent inspection for each feature across all inspection categories, in a format similar to this:

inspection_id | feature_id | date         | category
---------------|------------|--------------|-----------
1              | 1          | 10/10/2010   | inspection
2              | 1          | 10/20/2009   | cleaned
3              | 2          | 1/1/2008     | inspection

What would be the best way to formulate this query? I’ve attempted to use GROUP BY but haven’t succeeded in getting the anticipated results.

2

interesting problem! try using a window function with max() - select rows where date_created matches the max date for each feature_id + category combo. what error messages are you getting with group by? that might help us figure out what’s going wrong there.

3

try a CTE: WITH recent_inspections AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY feature_id, category ORDER BY date_created DESC) as rn FROM inspection) SELECT * FROM recent_inspections WHERE rn = 1; way cleaner!

4

A correlated subquery works great here. Just filter for records where the date_created matches the max date for each feature_id and category combo: SELECT id as inspection_id, feature_id, date_created as date, category FROM inspection i1 WHERE date_created = (SELECT MAX(date_created) FROM inspection i2 WHERE i2.feature_id = i1.feature_id AND i2.category = i1.category). This handles exactly what you need without window functions or CTEs - the grouping happens automatically through the correlated subquery.