[thelist] SQL Query

[John Townsend]

Jack,
If I understand your aim correctly, yes you do need to effectively do two selects - one to get the latest date & the other to get the info for that date, along the lines of:
select a.*, c1.*
from active_members a, charges c1
where a.id = c1.id 
   and c1.paid_on = (select max(c2.paid_on) from charges c2 where c2.id = a.id)
HTH
John

>  -------Original Message-------
>  From: Jack Timmons <jorachim at gmail.com>
>  To: thelist at lists.evolt.org
>  Subject: [thelist] SQL Query
>  Sent: 09 Jun '10 02:17
>  I think I'm having one of those days.
>  I'm trying to do a query to set up some data for an email. I'm doing
>  this in SQL instead of PHP because I can't trust others down the road
>  to replicate the PHP results exactly, but I can ensure the query
>  always does.
>  I have a table of customer charges. I want to find only the latest
>  paid_on for each customer. So, I did a query to order the results by
>  when it was paid_on descending, and grouped by ID.
>  Well, as you can imagine, no matter what I order in, I always get the
>  most recent charge.
>  Now, I know that doing the following query works out like I want it to:
>  SELECT * FROM (
>      SELECT *
>      FROM charges
>      ORDER BY paid_on DESC
>  ) as temp
>  GROUP BY id
>  My question: Is this the choice I have to take, or am I missing a key
>  thing so I only have to make one select statement? Unfortunately, this
>  Tuesday is Monday 2.0, so I'm not thinking as well as I should.
>  You can view the full mess here: http://pastebin.com/NfyaJ3ex
>  Also, if there's ways I can improve it, I'm open to suggestions.
>  --
>  -Jack Timmons
>  Blog: http://www.codeacula.com
>  Sandbox: http://www.trotlc.com
>  Twitter: @codeacula
>  Google Chat: Figure it out. C'mon, seriously.
>  --