Re: Help with join syntax sought

On May 19, 2009, at 11:29 PM, Andy Colson wrote:

> I'm not sure what this will do:
>
> HAVING
>        COUNT(fxr.currency_code_quote)  = 1
>
> The only time I have ever used HAVING is like:
>
> select name from something group by name having count(*) > 1
>
> to find duplicate name's.


That will leave out all results of multiple rows from the group by,
which is not the desired result I think. IIUC the desired result is to
leave out duplicates after the first match, not to leave out all
results that have duplicates.

I think you want something like: HAVING effective_from =
MAX(effective_from)

Or you ORDER BY effective_from DESC and use DISTINCT ON to ignore the
duplicates after the first match (which is the newest currency due to
the ordering).

I wonder whether it's possible to have effective_from dates in the
future though, that would complicate things slightly more...

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,4a133d4d10091830814072!