[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