[thelist] SQL Query
John Townsend
jft at worrigee.net
Tue Jun 8 19:31:06 CDT 2010
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.
> --
More information about the thelist
mailing list