|
Posted by strawberry on September 20, 2006, 9:29 am
Please log in for more thread options
. wrote:
> My query works OK this way, but I need every "kohde_name" only once
> (preferred the earliest "hav_date").
> I've tried with "DISTINCT kohde_name" in many ways, but always end up with
> syntax error...:(
> Is there any solution with DISTINCT, or do I need anotjer solution?
>
> my (simplified) query:
> SELECT henk_name,henk_number, kohde_name
> ,kohde_txt,havainto_id,hav_kohdetxt, hav_date
> FROM ((henkilo INNER JOIN havaitsija ON hja_henkilo_id=henkilo_id)
> INNER JOIN havainto ON hja_havainto_id=havainto_id)
> INNER JOIN kohde ON hav_kohde_id=kohde_id
> WHERE ...
>
> Tommi.P.
OK, I'm guessing a bit at your table structure here - not helped by my
decidedly limited knowledge of Finnish, but see if you can adapt this
to your needs (maybe as a derived table onto which everything else is
joined:
SELECT hto1 . *
FROM havainto hto1
LEFT JOIN havainto hto2 ON hto1.hav_kohdetxt = hto2.hav_kohdetxt
AND hto1.havainto_id <> hto2.havainto_id
AND hto1.hav_date < hto2.hav_date
WHERE hto2.hav_date IS NULL
| 
XML Sitemap