Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2011
    Posts
    4

    Unanswered: Nee a help with mysql query..

    I've a table with following records. It contains order id and order status id with status update date.
    I nee to fetch last status of each order.

    Code:
    id      ord_id  status_id        dt
    --------------------------------------
    1	1	1	2011-06-25 14:50:19
    2	1	2	2011-06-25 15:50:31
    3	2	1	2011-06-25 17:32:58
    4	2	2	2011-06-26 17:33:00
    5	2	3	2011-06-27 17:33:09
    6	2	4	2011-06-28 17:33:16
    the result should like as follows..
    Code:
    2	1	2	2011-06-25 15:50:31
    6	2	4	2011-06-28 17:33:16
    Can anyone suggest the query for this.. I've tried and not able to do it.
    I'm not sure whether the structure is ok or not. If there any problem with the table structure please suggest a better structure...

    thanks in advance...

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT t.id
         , t.ord_id  
         , t.status_id        
         , t.dt
      FROM ( SELECT ord_id
                  , MAX(dt) AS  max_dt
               FROM daTable
             GROUP
                 BY ord_id ) AS m
    INNER
      JOIN daTable AS t
        ON t.ord_id = m.ord_id
       AND t.dt = m.max_dt
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2011
    Posts
    4
    SELECT id, ord_id, status_id, MAX(dt)
    FROM tableName
    GROUP BY ord_id;
    Using this query we wont get correct status id and id..

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by jdcowboy View Post
    am i missing something here?
    yes, you are -- your query won't necessarily return the correct results

    please read ~jk groupwise max

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by jdcowboy View Post
    those attributes that do not appear in GROUP BY clause can appear in SELECT clause ONLY if they are aggregated.
    yes, that indeed is the problem

    other database systems will actually generate a syntax error if there are un-aggregated columns in the SELECT clause that are missing from the GROUP BY (what mysql calls "hidden" columns)

    you can try to fix this error by un-hiding them, adding them to the GROUP BY --
    Code:
    SELECT id
         , ord_id
         , status_id
         , MAX(dt)
      FROM daTable
    GROUP 
        BY id
         , ord_id
         , status_id
    but that's not actually a solution because there is only one row per id, and its date will be the maximum date for that id

    or, you can try to fix the error by making the hidden columns aggregates, thereby keeping the originally intended GROUP BY --
    Code:
    SELECT MIN(id)
         , ord_id
         , AVG(status_id)
         , MAX(dt)
      FROM daTable
    GROUP 
        BY ord_id
    but that's not actually a solution either, because the aggregate values that you get for the hidden columns won't necessarily come from the same row that has the maximum date

    that's why it's not a simple query

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by jdcowboy View Post
    Or there's actually no notable difference among them?
    i would say there isn't, but i have experience with database software (perhaps early versions, to be fair) where it did make a performance difference, often by insane orders of magnitude

    Quote Originally Posted by jdcowboy View Post
    Should we even consider the performance issues in the first place?
    yes, we should

    a general rule of thumb is that subqueries don't perform as well

    a more important factor might be whether your particular database system even supports a given solution -- for example, the row constructor version you posted has pretty sketchy support
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jun 2011
    Posts
    6

    Query

    SELECT *
    FROM (
    SELECT Order_ID , MAX(status_id)
    From Order
    Group by Order_ID
    ) TmpTbl T
    INNER JOIN Order O ON O.Order_ID = T.Order_ID

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    greenx, that's ~not~ the right solution, you're just appending the highest status onto every individual row in the group

    the original poster wanted the row with the last (i.e. datewise) status of each order

    you're returning every row in each order

    have another look at post #1

    then have a look at the correct solution, which is in post #2

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Tags for this Thread

Posting Permissions

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