SQL Sort by date

[Lee]

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

[Jerry Stuckle]

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.
[email]jstucklex@attglobal.net[/email]
==================

[Lee]

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.

[Johan]

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 - [url]www.chaosgeordend.nl[/url]
_____________________________________

[Bill Karwin]

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.

[Jerry Stuckle]

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.
[email]jstucklex@attglobal.net[/email]
==================

[Lee]

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!

[Gordon Burditt]

>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

[Rik]

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

[Kai Ruhnau]

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.

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

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

[Lee]

Damnit Bill, that works and I have no idea why! :) That's going to bug
me now. I don't see why order_id should be NULL, but I'll have to
puzzle it out on my own nickel.

The other answers were good too; I never thought of case or ENUM.

Thanks all.

Lee

[Bill Karwin]

Lee wrote:

> Damnit Bill, that works and I have no idea why! :) That's going to bug
> me now. I don't see why order_id should be NULL, but I'll have to
> puzzle it out on my own nickel.

It's a left outer join trick, to get the least/greatest record in a group.

One could write it in English like: "show me the record for a given
order_id for which no other record exists with the same order_id and a
greater timestamp."

One can test for no record existing is to do an outer join, and check if
a column in the smaller side of the join is NULL (best to use a column
that is otherwise constrained as not null).

Regards,
Bill K.

[Johan]

Johan <jovo@NOSPAMknoware.nl> wrote:

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

And after reading my own contribution over again,
of course MAX should be used here...
with set = 3, go = 2 and ready = 1

Grts
Johan vO

--
_____________________________________
Ing. Johan van Oostrum
chaos geordend - [url]www.chaosgeordend.nl[/url]
_____________________________________

[Johan]

Johan <jovo@NOSPAMknoware.nl> wrote:

> Johan <jovo@NOSPAMknoware.nl> wrote:

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

> And after reading my own contribution over again,
> of course MAX should be used here...
> with set = 3, go = 2 and ready = 1
>
> Grts
> Johan vO

Sorry, that was a not-intentioned contribution...
Go for the options in the other threads [subselect and use of ENUM]
--
_____________________________________
Ing. Johan van Oostrum
chaos geordend - [url]www.chaosgeordend.nl[/url]
_____________________________________