0

I have this stockmoves table :

id  serial  op_date   from  to
23  aaa1    2014-11-10  0   8
24  aaa2    2014-11-10  0   8
25  aaa3    2014-11-10  0   8
26  aaa1    2014-11-18  8   1
27  aaa2    2014-11-15  8   9
28  aaa2    2014-11-19  9   8
29  aaa2    2014-11-20  8   10

(Please ignore the column names, I shortened them to be more succinct just for posting here even if they might be reserved sql names.)

And I want to extract a unique list of serials with the latest date they have for a certain "to" (location where the product's sent). If I do: 

SELECT serial, MAX(op_date) as max_op_date
FROM stockmoves 
WHERE to = 8
GROUP BY serial;

I get the expected (good) answer: 

serial  max_op_date
aaa1    2014-11-10
aaa2    2014-11-19
aaa3    2014-11-10

However, if I add in the results the "from" column, like this:

SELECT serial, from, MAX(op_date) as max_op_date
FROM stockmoves 
WHERE to = 8
GROUP BY serial;

I get an incorrect answer for serial aaa2 imho:

serial from max_op_date 
aaa1    0   2014-11-10 
aaa2    0   2014-11-19 
aaa3    0   2014-11-10

instead of the expected:

serial from max_op_date
aaa1    0   2014-11-10
aaa2    9   2014-11-19
aaa3    0   2014-11-10

Am I missing something or my old version of MySQL (I am forced to use) has a problem? Shouldn't correspond the "from" value to the line that has the max(op_date) value?

Thank you in advance,

Lian

EDIT:

Ok, following suggestions here is a more complex statement that does what I need:

SELECT sm1.serial, sm1.op_date, sm1.from
FROM stockmoves sm1
JOIN (
        SELECT serial, max(id) as max_id
        FROM stockmoves 
        WHERE to = 8
        GROUP BY serial
) sm2 ON (sm2.max_id = sm1.id)

Thank you!!!

Lian Sebe
  • 101
  • 1
  • 3
  • `from` is a reserved word, so perhaps there's something you're not telling us. Anyway, if you fail to group by all of the (non-aggregated) columns in the SELECT, then the result will be uncertain. This is standard behaviour in MySQL - other DBs will error out. – Strawberry Nov 20 '14 at 10:43
  • Please see the italics paragraph from my original post. I already said "from", "to" etc are not the real names, but something that should have ease-up debugging here. Maybe I was wrong as I can see this is confusing... It is "loc_from" instead of "from", "loc_to" instead of "to" and so on. I see your other point though, and I'll think about an alternative then... Thank you. – Lian Sebe Nov 20 '14 at 11:43

0 Answers0