Databases
StackOverflow2013
Postsdbo
POHow do I sort the result from SQL JOIN's? (Groupwise max?)
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow do I sort the result from SQL JOIN's? (Groupwise max?)
    text
    copied!<p>I'm having some issues with this query. What I want to do is select specific information about my users, but also include the latest dates of their latest campaign, and latest invoice.</p> <p>I'm currently getting results returned, but the invoice and campaign data is not sorted by date. </p> <pre><code>SELECT cu.uid, cu.email, cu.company, cu.phone, cu.first_name, cu.last_name, cu.city, cu.crdate, sc.crdate, invoice.total FROM `corp_users` cu LEFT JOIN sponsored_campaigns AS sc ON cu.uid=sc.userid LEFT JOIN corp_invoices AS invoice ON cu.uid=invoice.userid WHERE cu.company != "" GROUP BY cu.uid ORDER BY cu.crdate DESC </code></pre> <p>I've read this resource (<a href="http://jan.kneschke.de/projects/mysql/groupwise-max/" rel="nofollow">http://jan.kneschke.de/projects/mysql/groupwise-max/</a>) about groupwise-max, but I'm not able to get it to work properly. After following the steps by code looks like this, and in the return rows it always display NULL on the dates of the users.</p> <pre><code>SELECT cu.uid, cu.email, cu.company, cu.phone, cu.first_name, cu.last_name, cu.city, cu.crdate, sc.crdate, invoice.total FROM `corp_users` cu LEFT JOIN ( SELECT a.uid, MAX(b.crdate) MaxInvoice, MAX(c.crdate) MaxCampaign FROM corp_users a LEFT JOIN corp_invoices b ON a.uid=b.userid LEFT JOIN sponsored_campaigns c ON a.uid=b.userid GROUP BY a.uid ) temp ON cu.uid=temp.uid LEFT JOIN sponsored_campaigns AS sc ON cu.uid=sc.userid AND sc.crdate=temp.MaxCampaign LEFT JOIN corp_invoices AS invoice ON cu.uid=invoice.userid AND invoice.crdate=temp.MaxInvoice WHERE cu.company != "" GROUP BY cu.uid ORDER BY cu.crdate DESC </code></pre> <p>To summarize my question. How do I return the result where sc.crdate and invoice.crdate is the latest of each table.?</p>
 

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