[thelist] SQL Query
Christopher Marsh
Christopher.Marsh at akqa.com
Wed Jun 9 04:30:53 CDT 2010
Jack
On Tue, Jun 8, 2010 at 12:06 PM, Christopher Marsh
<Christopher.Marsh at akqa.com> wrote:
> > Would the following satisfy your criteria?
> >
> > SELECT MAX(paid_on),
> > id
> > FROM charges
> > GROUP BY id
>
> It does for the explanation I gave, except I missed out a vital part
> that you'd have only figured out if you could see through the blood in
> your eyes after looking at my query.
>
> Each charge has a related amount. I need the most recent charge,
> because I have to perform logic against the amount back.
How about the something along the following lines? Caveat: SQL isn't really my area of expertise, so if it *does* appear to work, *do* double check it.
SELECT a.*,
c1.*
FROM active_members a
JOIN charges c1
ON a.id = c1.id
JOIN (SELECT MAX(paid_on) AS paid_on,
id
FROM charges
GROUP BY id) c2
ON c1.paid_on = c2.paid_on
AND c1.id = c2.id
Cheers!
--
Christopher Marsh
More information about the thelist
mailing list