Rows having maximum group value in MySQL

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.


    Requires Free Membership to View

    Login

Your SQL is fine. There's actually another way you could have done it:

select A.OrderID, A.LinkID, A.InvoiceDate
  from aorders A
inner
  join ( select LinkID
              , max(InvoiceDate) as MaxDate
           from aorders
       group by LinkID ) M
    on A.LinkID = M.LinkID
   and A.InvoiceDate = M.MaxDate

Unfortunately, this won't work in MySQL either. In both cases, it's because MySQL doesn't support subqueries.

If you refer to the MySQL documentation, they discuss this problem. In 3.5.4 The Rows Holding the Group-wise Maximum of a Certain Field, they suggest using a temp table to hold the results of the grouped maximum values. Then a second query would join to the temp table. It's basically just like the inner join above, except in two steps.

For More Information


This was first published in November 2002

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top