Lee <lhenkel@gmail.com> wrote:

> This is probably very trivial, but I'm stuck. I have a audit log table
> like this:
>
> Order_Id Status Timestamp
> 12345 READY 2006-04-28 09:03:21
> 43244 READY 2006-04-28 09:03:30
> 66434 READY 2006-04-28 09:04:17
> 12345 SET 2006-04-28 09:05:46
> 12345 GO 2006-04-28 09:10:49
> 43244 SET 2006-04-28 09:17:38
> 99999 READY 2006-04-29 03:12:33
>
> How can I write a query that gives me all the order_ids who's status is
> 'SET'?
>
> I've been trying to use the MAX,MIN and group by functions, but not
> getting the results I would expect.
>
> Thanks for any help,
> Lee
Not tested this but using a subselect might be an option for you. Try
something like this:

Select l.order_id, min(l.status)
from
(select order_id, case status
when 'SET' then 1
when 'GO' then 2
when 'READY' then 3
else 4
end case as status
from log_table) l
group by l.order_id;

Again, no syntax check performed on this!
--
_____________________________________
Ing. Johan van Oostrum
chaos geordend - www.chaosgeordend.nl
_____________________________________