Problem solve Get help with specific problems with your technologies, process and projects.

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
  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 last published in November 2002

Dig Deeper on Oracle and SQL



Find more PRO+ content and other member only offers, here.

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.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.