CODEWITHSUNDEEP
sql
Mike Goodwin
Mike Goodwin

Reputation: 3

SQL Query help - using max() twice on same column

I tried searching around for this, but honestly I'm not really sure exactly what to search on...so be patient if this has been asked before.

I have a table that has a list of events like this:

Date      Event 
-----     ------
1/1/2012  Event1
1/2/2012  Event1
1/4/2012  Event2
1/6/2012  Event1
etc.

What I am trying to do is do a select on this table, returning both the most recent date that Event1 occured and the most recent date that Event2 occured. So using the above table as an example, I'd wish to see the following results:

Event 1      Event2
-------      ------
1/6/2012     1/4/2012

Any ideas on how to acheive this? I know how to get these values individually

SELECT max(date) FROM table WHERE Event LIKE 'Event1'

just not together as one result.

Upvotes: 0

Views: 623

Answers (2)

jelies
jelies

Reputation: 9290

Try this query for any value on event column: 

select MAX(date), event
from events_table
group by event;

Upvotes: 0

Taryn
Taryn

Reputation: 247840

You can use a CASE statement with the MAX() function.

SELECT MAX(CASE WHEN event = 'Event1' THEN date END) Event1, 
    MAX(CASE WHEN event = 'Event2' THEN date END) Event2
FROM yourTable

See SQL Fiddle with Demo

Upvotes: 3

Related Questions