Databases
StackOverflow2013
Postsdbo
POHow to SELECT the MAX(value) in a GROUP BY statement in MySQL
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to SELECT the MAX(value) in a GROUP BY statement in MySQL
    text
    copied!<h2>Background</h2> <p>I am pivoting this data using the <code>CASE</code> expression for years 2010, 2011, and 2012 and grouping by company. The "measure" that is being pivoted/returned is the <code>max(state)</code> for each case/year and it's working great.</p> <h2>Problem</h2> <p>I am trying to get the most recent record <code>max(invoice_date)</code> from all records for each <code>company_name</code>, but the query is only returning the <code>date</code> where the <code>CASE</code> expression is satisfied instead of the <code>max(invoice_date)</code>.</p> <h2>Here is an example of the table data:</h2> <pre><code>company_name | invoice_date | year | state -------------------------------------------------------- CompanyA | 20130101 | 2012 | 1 CompanyA | 20130101 | 2012 | 3 CompanyB | 20130102 | 2012 | 2 CompanyA | 20120103 | 2011 | 3 CompanyB | 20120104 | 2011 | 1 CompanyB | 20120104 | 2011 | 3 CompanyA | 20110101 | 2010 | 4 CompanyB | 20110105 | 2010 | 2 </code></pre> <h2>And here is an example of the desired view:</h2> <pre><code>company_name | invoice_date | 2010 | 2011 | 2012 -------------------------------------------------------------- CompanyA | 20130101 | 4 | 3 | 3 CompanyB | 20130102 | 2 | 3 | 2 </code></pre> <h2>And here is an example of the result that I get:</h2> <pre><code>company_name | invoice_date | 2010 | 2011 | 2012 -------------------------------------------------------------- CompanyA | 20110101 | 4 | 3 | 3 CompanyB | 20110105 | 2 | 3 | 2 </code></pre> <p>Please note the <code>date</code> is in 2011 for both CompanyA and CompanyB which is not the most recent date in the table for those two companies. I am trying to get the <code>max(invoice_date)</code>.</p> <h2>Solved:</h2> <pre><code>select company_name AS company_name, max(invoice_date) AS invoice_date, max((case when (year = 2010) then state else 0 end)) AS `2010`, max((case when (year = 2011) then state else 0 end)) AS `2011`, max((case when (year = 2012) then state else 0 end)) AS `2012`, from tbl group by company_name order by invoice_date desc </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload