QUESTION POSED ON: 14 November 2004
I really need your help to solve this SQL problem,
and here is the problem. I have this aorders table:
+----------+---------+-------------+
| OrderID | LinkID | InvoiceDate |
+----------+---------+-------------+
| aA000012 | iA00001 | 2002-09-28 |
| aA000013 | iA00001 | 2002-10-28 |
| aA000022 | iA00002 | 2002-10-03 |
| aA000034 | iA00003 | 2002-10-04 |
| aA000035 | iA00003 | 2002-11-04 |
| aA000041 | iA00004 | 2002-10-11 |
| aA000042 | iA00004 | 2002-11-11 |
+----------+---------+-------------+
I need to show this result:
+----------+---------+-------------+
| OrderID | LinkID | InvoiceDate |
+----------+---------+-------------+
| aA000013 | iA00001 | 2002-10-28 |
| aA000022 | iA00002 | 2002-10-03 |
| aA000035 | iA00003 | 2002-11-04 |
| aA000042 | iA00004 | 2002-11-11 |
+----------+---------+-------------+
So I tried:
SELECT OrderID,LinkID,InvoiceDate
FROM aorders A
WHERE InvoiceDate =
(SELECT MAX(InvoiceDate)
FROM aorders
WHERE LinkID = A.LinkID)
Unfortunately, I received this error:
ERROR 1064: You have an error
in your SQL syntax near 'SELECT MAX(InvoiceDate)
FROM aorders WHERE LinkID = A.LinkID)' at line 4
Could you please tell me why I get this error?
Is there a way to show only the rows containing
the latest InvoiceDate? I am using mySQL, so please
help me out.
|