Professional Web Applications Themes

SQL Sort by date - MySQL

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

  1. #1

    Default SQL Sort by date

    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

    Lee Guest

  2. #2

    Default Re: SQL Sort by date

    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]jstucklexattglobal.net[/email]
    ==================
    Jerry Stuckle Guest

  3. #3

    Default Re: SQL Sort by date

    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.

    Lee Guest

  4. #4

    Default Re: SQL Sort by date

    Lee <lhenkel> 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]
    _____________________________________
    Johan Guest

  5. #5

    Default Re: SQL Sort by date

    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.
    Bill Karwin Guest

  6. #6

    Default Re: SQL Sort by date

    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]jstucklexattglobal.net[/email]
    ==================
    Jerry Stuckle Guest

  7. #7

    Default Re: SQL Sort by date

    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!

    Lee Guest

  8. #8

    Default Re: SQL Sort by date

    >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
    Gordon Burditt Guest

  9. #9

    Default Re: SQL Sort by date

    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


    Rik Guest

  10. #10

    Default Re: SQL Sort by date

    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.
    Kai Ruhnau Guest

  11. #11

    Default Re: SQL Sort by date

    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

    Lee Guest

  12. #12

    Default Re: SQL Sort by date

    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.
    Bill Karwin Guest

  13. #13

    Default Re: SQL Sort by date

    Johan <jovoNOSPAMknoware.nl> wrote:
    > Lee <lhenkel> 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 Guest

  14. #14

    Default Re: SQL Sort by date

    Johan <jovoNOSPAMknoware.nl> wrote:
    > Johan <jovoNOSPAMknoware.nl> wrote:
    > > Lee <lhenkel> 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]
    _____________________________________
    Johan Guest

Similar Threads

  1. SORT BY DATE
    By Andrew Turek in forum Adobe Photoshop Elements
    Replies: 7
    Last Post: October 12th, 08:08 PM
  2. Sort by Date (Latest Date that is)
    By Speegs in forum Dreamweaver AppDev
    Replies: 1
    Last Post: March 11th, 12:48 PM
  3. datagrid sort by date
    By rsj@gisportalen.dk in forum ASP.NET Data Grid Control
    Replies: 0
    Last Post: January 19th, 07:00 PM
  4. Sort on Date Column
    By Alan Z. Scharf in forum ASP.NET Data Grid Control
    Replies: 1
    Last Post: December 5th, 09:38 PM
  5. >>Here's a Datagrid sort by date AS<<
    By N Rohler in forum Macromedia Flash Actionscript
    Replies: 0
    Last Post: February 26th, 01:49 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139