From: | Alban Hertroys <dalroi(at)solfertje(dot)student(dot)utwente(dot)nl> |
---|---|
To: | Andy Colson <andy(at)squeakycode(dot)net> |
Cc: | pgsql-general(at)postgresql(dot)org, "James B(dot) Byrne" <byrnejb(at)harte-lyne(dot)ca> |
Subject: | Re: Help with join syntax sought |
Date: | 2009-05-19 23:14:16 |
Message-ID: | 08544009-B730-4359-9851-7E705C39E123@solfertje.student.utwente.nl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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!
From | Date | Subject | |
---|---|---|---|
Next Message | Jeffrey Trimble | 2009-05-19 23:23:11 | Re: Configure fails to find readline libraries |
Previous Message | Scott Mead | 2009-05-19 23:10:24 | Re: Configure fails to find readline libraries |