Q

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.


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

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close