Useful SQL

Imagine an audit table of deal histories where every change to the deal means one more row in the table for a given deal ID.

How do you find the deals with the first audit time within a given time window?

[I'm posting this on my blog because I come across this problem quite often but never seem to remember the solution. By putting it here, I can copy-and-paste it in the future :) ]

So, imagine the audit table for deal_history looks a little like this:

+----+----------------+----------------+
|name|last_update_time|last_update_user|...
+----+----------------+----------------+

Such that the data looks like:

+-------+-------------------+----+--
|deal #1|03/14/2011 14:00:00|Tom |...
+-------+-------------------+----+--
|deal #1|03/14/2011 15:33:33|Dick|...
+-------+-------------------+----+--
|deal #2|03/14/2011 16:22:22|Tom |...
+-------+-------------------+----+--
| ... ... ..
+-------+-------------------+----+--

The trick is to select over the same table twice thus:

select d1.last_update_time, d1.name, d1.last_update_user
from deal_history d1 , deal_history d2
where d1.last_update_time >= '03/14/2011 14:00:00'
and d1.last_update_time <= '03/21/2011 15:00:00'
and d1.deal_id = d2.deal_id
group by d1.last_update_time, d1.name , d1.last_update_user
having d1.last_update_time = min(d2.last_update_time)

Making sure the unique identifier for a give deal is equal in both sets of data (note: in our example, this is not a PK since there are many rows per deal ID).

Also, note that the group by clause has fields in the same order and that the having clause must follow the group by.