SQL: Select most recent date for each category

Question

I'm pulling data from a database and one of the tables contain two columns that together identify a location and another column containing a date of each time it was serviced. Is it possible to write a SQL query such that I get the most recent time that each location was serviced?

So if my raw data looks like this:

category_a  category_b  date
       1           a     1/1/01
       1           a     2/2/02
       1           b     1/2/01
       1           b     2/3/02
       2           a     1/3/01
       2           a     2/4/02
       2           b     1/4/01
       2           b     2/5/02

then the query would return this:

category_a  category_b  date
       1           a     2/2/02
       1           b     2/3/02
       2           a     2/4/02
       2           b     2/5/02

This would be easy to do if the database was authored in such a way that the category combinations were stored in a separate table. However, I don't control this database, so I can't make changes to it.

I was wondering the same. I'm no SQL expert, but I thought I explained my question well... — Wilduck, Jul 13 '11 at 14:40
Maybe you upset someone on another thread, and they retaliated by dinging you here. I've had that happen before. — , Jul 13 '11 at 18:05

score 29 · Accepted Answer · answered Jul 13 '11 at 14:35

29

SELECT
    category_a,
    category_b,
    MAX(date)
FROM
    Some_Unnamed_Table
GROUP BY
    category_a,
    category_b
ORDER BY
    category_a,
    category_b

I certainly don't mind helping people when I can, or I wouldn't be on this site, but did you really search for an answer for this before posting?

answered Jul 13 '11 at 14:35

Tom H

46,766
14
87
128

21

I did. Unfortunately, I didn't have the words to explain this in a google-able fashion. In fact, I ended up reading about CASE statements for 15 minutes before I said "Screw it, someone on SO knows how to answer this." I really do appreciate the help. – Wilduck Jul 13 '11 at 14:46
4

Understood. Glad that I could help. – Tom H Jul 13 '11 at 14:48

score 5 · Answer 2 · answered Jul 13 '11 at 14:33

This is a simple "group by" using the fact the the "most recent" date is the "highest valued", or max(), date

select category_a, category_b, max(date)
from mytable
group by category_a, category_b
order by category_a, category_b -- The ORDER BY is optional, but the example data suggests it's needed

score 2 · Answer 3 · answered Jul 13 '11 at 14:35

2

select category_a, category_b, max( date) from tbl group by category_a ,category_b;

answered Jul 13 '11 at 14:35

Muhammad Usama

2,797
1
17
14

score 2 · Answer 4 · answered Jul 13 '11 at 14:37

2

Try

select category_a, category_b, max(date) as last_update from table group by category_a, category_b

answered Jul 13 '11 at 14:37

BugFinder

17,474
4
36
51

SQL: Select most recent date for each category

4 Answers4

Linked

Related