sql - Understanding correlation in mysql -
i have table duplicate ids
representing person has placed order. each of these orders has date. each order has status code 1 - 4. 4 means cancelled order. using following query:
select personid, max(date), status orders status = 4 group personid
the problem is, while return unique record each person recent order date, not give me correct status. in other words, assumed status correctly correlated max(date) , not. pulls, seemingly @ random, 1 of statuses 1 of orders. can add specificity say, in basic terms, give me exact status same record whatever max(date) is.
unfortunately, there no simple way want. other rdbms vendors don't consider queries using aggregate functions valid unless non-aggregated result fields in group by. general solution these kinds of questions involves subquery "last" records, joined original table rows.
depending on structure of data may or may not possible. instance, if have multiple rows same personid
, date
there no way determine alone one's status
should used.
Comments
Post a Comment