Nee a help with mysql query..

nithinics · #1 (**permalink**) 06-27-11, 00:12

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

r937 · #2 (**permalink**) 06-27-11, 00:25

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

nithinics · #3 (**permalink**) 06-30-11, 04:53

Quote:

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

r937 · #4 (**permalink**) 06-30-11, 06:52

Quote:

Originally Posted by jdcowboy

am i missing something here?

yes, you are -- your query won't necessarily return the correct results

please read ~jk groupwise max

r937 · #5 (**permalink**) 06-30-11, 13:03

Quote:

Originally Posted by jdcowboy

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

r937 · #6 (**permalink**) 06-30-11, 13:09

Quote:

Originally Posted by jdcowboy

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

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

greenx · #7 (**permalink**) 07-02-11, 10:31

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

r937 · #8 (**permalink**) 07-02-11, 13:44

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

Internet Services

Web Development

Digital Marketing

Consumer Tech