SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Zealot
    Join Date
    Sep 2004
    Location
    Boston
    Posts
    174
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    distinct/count/group by across tables and more!

    OK, I think I got everything in that subject line...

    I'm creating an order tracking system (I'm sure that's never been done before!) and it includes an order_history table where I track changes to the order status. The main fields in the table look like this

    Code:
    CREATE TABLE `order_history` (
      `recordID` int(11) NOT NULL auto_increment,
      `orderID` int(11) NOT NULL default '0',
      `ts` int(11) default NULL,
      `statusID` smallint(4) default NULL,
      `userID` int(11) default NULL,
      `notes` text,
      PRIMARY KEY  (`recordID`),
      KEY `orderID` (`orderID`),
      KEY `statusID` (`statusID`)
    ) TYPE=MyISAM
    using this table I can locate an order's current status

    Code:
    select statusID from order_history where orderID = 1234 order by ts desc limit 1
    since the most recent entry for the order must be it's current status. I can also find the history of the order

    Code:
    select from_unixtime(ts) as statusdate, statusID, notes from order_history where orderID = 1234 order by ts
    This all works well. Now I need to generate a report on how many orders are at each status level. If it were just one entry per order it would be something like this:

    Code:
    select statusID, count(orderID) as cID from order_history group by statusID order by statusID
    But that yields incorrect results since it tracks older records too. Thus an order that has moved from status 1 to 2 to 3 shows up three times!

    I have a solution that works with a temporary table, but I'd rather do this in a single query if it's possible.

    The next step involves joining the order_history table with the main orders table...the rest of the order information is kept in the orders table. Things like the orderdate, inv_name, etc. How could I formulate a query to join across the orders and order_history tables to give me a row that contains (for example) the orderID, orderdate, inv_name, and current status?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    number of orders at each current status --
    Code:
    select statusID
         , count(orderID) as cID 
      from order_history as X
     where ts
         = ( select max(ts)
               from order_history
              where orderID = X.orderID )
    group 
        by statusID 
    order 
        by statusID
    joining to orders table --
    Code:
    select X.orderID
         , orderdate
         , inv_name
         , X.statusID
      from order_history as X
    inner
      join orders
        on X.orderID = orders.orderID
     where X.ts
         = ( select max(ts)
               from order_history
              where orderID = X.orderID )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Sep 2004
    Location
    Boston
    Posts
    174
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    WOW! r937 strikes again!

    OK, now can it be done without subqueries?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    yes, but unfortunately i have this policy, i don't rewrite perfectly good queries that i already spent time on, because the poster forgot to mention that they are on an older release

    i think the onus should be on the poster to mention this, so as to prevent people from wasting their time on solutions that won't work until you upgrade

    sorry, hope you understand

    perhaps someone else can jump in

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot
    Join Date
    Sep 2004
    Location
    Boston
    Posts
    174
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No problem! I certainly understand.

    So...how about just a hint then and leave the rest "as an exercise to the reader"!

    Thanks

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    number of orders at each current status --
    Code:
    select X.statusID
         , count(X.orderID) as cID 
      from order_history as X
    inner
      join order_history as Y
        on X.orderID = Y.orderID   
    group 
        by X.statusID 
    having X.ts
         = max(Y.ts)
    order 
        by X.statusID
    joining to orders table --
    Code:
    select X.orderID
         , orders.orderdate
         , orders.inv_name
         , X.statusID
      from order_history as X
    inner
      join order_history as Y
        on X.orderID = Y.orderID   
    inner
      join orders
        on X.orderID = orders.orderID
    group 
        by X.orderID
         , orders.orderdate
         , orders.inv_name
         , X.statusID
    having X.ts
         = max(Y.ts)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •