SEO

#1 (**permalink**) 02-28-2008, 07:38 AM

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

#2 (**permalink**) 02-28-2008, 07:38 AM

Lee 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
>

SELECT Order_Id FROM mytable where Status = 'SET';

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

#3 (**permalink**) 02-28-2008, 07:38 AM

Jerry: The problem with that is since this is an log, when someone
places an entry afterwards saying order_id is 'GO', your query will
always show that order_id as set. In my example table, 12345 would
still show as 'SET' even though it's actually 'GO'

I could do an update instead of an insert, but I want to track how long
each step takes. I could also write another table with order_ids and
just do update statements on it. It just bothers me I can't do it with
just this table.

#4 (**permalink**) 02-28-2008, 07:38 AM

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
_____________________________________

#5 (**permalink**) 02-28-2008, 07:39 AM

Lee 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'?

SELECT a1.order_id, a1.status
FROM audit_log AS a1
LEFT OUTER JOIN audit_log AS a2
ON a1.order_id = a2.order_id AND a1.`timestamp` < a2.`timestamp`
WHERE a2.order_id IS NULL AND a1.status = 'SET'

Regards,
Bill K.

#6 (**permalink**) 02-28-2008, 07:39 AM

Lee wrote:
> Jerry: The problem with that is since this is an log, when someone
> places an entry afterwards saying order_id is 'GO', your query will
> always show that order_id as set. In my example table, 12345 would
> still show as 'SET' even though it's actually 'GO'
>
> I could do an update instead of an insert, but I want to track how long
> each step takes. I could also write another table with order_ids and
> just do update statements on it. It just bothers me I can't do it with
> just this table.
>

OK, I see your problem now.

That's going a little harder. If you have a version of MySQL which supports
subselects, you could have something like:

SELECT Order_Id FROM mytable
WHERE Status = 'SET' AND
Order_Id NOT IN (SELECT Order_Id
FROM mytable
WHERE Status = 'GO');

This will get all orders with the status of SET which do not also have the
status of GO. You could add checks for other status values, also.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

#7 (**permalink**) 02-28-2008, 07:39 AM

Thanks for all the replies.. sadly I'm at home and can't try these out,
but at least these are some good starts if not solutions. I came up
with something that appears to work doing:

SELECT * FROM mytable
GROUP BY order_id
ORDER BY TIMESTAMP

Which gives the current status of all orders (I think). However, I
still have to filter in code for the status. Not terribly elegent.

Thanks!

#8 (**permalink**) 02-28-2008, 07:39 AM

>Thanks for all the replies.. sadly I'm at home and can't try these out,
>but at least these are some good starts if not solutions. I came up
>with something that appears to work doing:
>
>SELECT * FROM mytable
>GROUP BY order_id
>ORDER BY TIMESTAMP
>
>Which gives the current status of all orders (I think). However, I
>still have to filter in code for the status. Not terribly elegent.

If you have multiple records for a given order_id, with different
values of status, you get ONE value of status returned. It is
not necessarily the first status, or the last status, or the status
associated with the timestamp returned.

Gordon L. Burditt

#9 (**permalink**) 02-28-2008, 07:39 AM

Jerry Stuckle wrote:
> OK, I see your problem now.
>
> That's going a little harder. If you have a version of MySQL which
> supports subselects, you could have something like:
>
> SELECT Order_Id FROM mytable
> WHERE Status = 'SET' AND
> Order_Id NOT IN (SELECT Order_Id
> FROM mytable
> WHERE Status = 'GO');
>
> This will get all orders with the status of SET which do not also
> have the status of GO. You could add checks for other status values,
> also.

Hmmm, damned, thought I would nail it with an ENUM field, but no:
For MIN(), MAX(), and other aggregate functions, MySQL currently compares
ENUM and SET columns by their string value rather than by the string's
relative position in the set.

That could have solved a lot, but no....
:
SELECT Order_ID
FROM
(SELECT Order_ID, MAX(Status) AS 'max_status'
FROM mytable
GROUP BY Order_ID) as x
WHERE x.`max_status` = 'SET';

Let's wait for a correct implementation...

Grtz,
--
Rik Wasmus

#10 (**permalink**) 02-28-2008, 07:39 AM

Rik wrote:
> Hmmm, damned, thought I would nail it with an ENUM field, but no:
> For MIN(), MAX(), and other aggregate functions, MySQL currently compares
> ENUM and SET columns by their string value rather than by the string's
> relative position in the set.
>
> That could have solved a lot, but no....
> :
> SELECT Order_ID
> FROM
> (SELECT Order_ID, MAX(Status) AS 'max_status'
> FROM mytable
> GROUP BY Order_ID) as x
> WHERE x.`max_status` = 'SET';
>
> Let's wait for a correct implementation...

You can get the numeric index of the enum field as described in the manual.

http://dev.mysql.com/doc/refman/5.0/en/enum.html

<snip>

If you retrieve an ENUM value in a numeric context, the column value's
index is returned. For example, you can retrieve numeric values from an
ENUM column like this:

mysql> SELECT enum_col+0 FROM tbl_name;

</snip>

Greetings
Kai

--
This signature is left as an exercise for the reader.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

SQL Sort by date