SQL: Select most recent date for each category

sql

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.

Best Answer

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?

Related Solutions

Sql – How to perform an IF…THEN in an SQL SELECT

The CASE statement is the closest to IF in SQL and is supported on all versions of SQL Server.

SELECT CAST(
             CASE
                  WHEN Obsolete = 'N' or InStock = 'Y'
                     THEN 1
                  ELSE 0
             END AS bit) as Saleable, *
FROM Product

You only need to use the CAST operator if you want the result as a Boolean value. If you are happy with an int, this works:

SELECT CASE
            WHEN Obsolete = 'N' or InStock = 'Y'
               THEN 1
               ELSE 0
       END as Saleable, *
FROM Product

CASE statements can be embedded in other CASE statements and even included in aggregates.

SQL Server Denali (SQL Server 2012) adds the IIF statement which is also available in access (pointed out by Martin Smith):

SELECT IIF(Obsolete = 'N' or InStock = 'Y', 1, 0) as Saleable, * FROM Product

Sql – How to return only the Date from a SQL Server DateTime datatype

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, @your_date))

for example

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))

gives me

2008-09-22 00:00:00.000

Pros:

No varchar<->datetime conversions required
No need to think about locale

Best Answer

Related Solutions

Sql – How to perform an IF…THEN in an SQL SELECT

Sql – How to return only the Date from a SQL Server DateTime datatype

Related Topic