sql works in libreoffice base but not libre basic

Question

I have a classic question about getting the latest record from a table. Here is the statement:

SELECT t1.Item, t1.Price, t1.ODate
FROM Order AS t1
JOIN ( (SELECT Item, Price, Max(ODate) As MaxDate
        FROM Order
        GROUP BY Item ) As t2 )
ON t1.Item = t2.Item and t1.Odate = MaxDate
ORDER BY t1.Item ASC

This statement works on both phpMyAdmin and LibreOffice base, but LibreOffice basic gives me a Message: Syntax error in SQL expression.

Any help?

score 1 · Answer 1 · edited Jul 26 '15 at 10:23

Maybe you should remove "Price" from t2's selection. It seems nonsense in the join, and will cause error message below in MSSMS 2008 which is in my case.

Column 'Order.Price' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Try

SELECT t1.Item, t1.Price, t1.ODate
FROM Order AS t1
JOIN  (SELECT Item, Max(ODate) As MaxDate
        FROM Order
        GROUP BY Item ) As t2 
ON t1.Item = t2.Item and t1.Odate = MaxDate
ORDER BY t1.Item ASC

sql works in libreoffice base but not libre basic

1 Answers1