roemhildtg roemhildtg - 4 years ago 84
SQL Question

SQL Query - latest inspection for each type of feature and category of inspection

I have two tables -

feature
and
inspection
.

The feature table looks like this:

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


The inspection table looks like this:

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


How can I create a query that would give me the most recent inspection for each feature in each category?

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

Answer Source

If I understand correctly, this is just choosing the most recent row from a group. The most common method uses row_number():

select i.*
from (select i.*,
             row_number() over (partition by feature_id, category order by date desc) as seqnum
      from inspections i
     ) i
where seqnum = 1;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download