Home > Ask the Oracle Database / Applications Experts > Questions & Answers > Rows having maximum group value in MySQL
Ask The Oracle Expert: Questions & Answers
EMAIL THIS

Rows having maximum group value in MySQL

Rudy Limeback EXPERT RESPONSE FROM: Rudy Limeback

Pose a Question
Other Oracle Categories
Meet all Oracle Experts
Become an Expert for this site
>
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.


>

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


Digg This!    StumbleUpon Toolbar StumbleUpon    Bookmark with Delicious Del.icio.us   



RELATED RESOURCES
2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems
Search Bitpipe.com for the latest white papers and business webcasts
Whatis.com, the online computer dictionary



Search and Browse the Expert Answer Center
Search and browse more than 25,000 question and answer pairs from more than 250 TechTarget industry experts.
Browse our Expert Advice



Oracle White Papers: Fusion Middleware
HomeNewsTopicsTipsAsk the ExpertsMultimediaWhite PapersProductsBlogs
About Us  |  Contact Us  |  For Advertisers  |  For Business Partners  |  Site Index  |  RSS
SEARCH 
TechTarget provides technology professionals with the information they need to perform their jobs - from developing strategy, to making cost-effective purchase decisions and managing their organizations' technology projects - with its network of technology-specific websites, events and online magazines.

TechTarget Corporate Web Site  |  Media Kits  |  Site Map




All Rights Reserved, Copyright 2003 - 2009, TechTarget | Read our Privacy Policy
  TechTarget - The IT Media ROI Experts