Re: distinct doesn't work when I include date

From: <drdave_at_canoemail.com>
Date: 26 Jul 2006 12:15:35 -0700
Message-ID: <1153941335.549047.283350@m73g2000cwd.googlegroups.com>

k the trunc worked but not perfectly as I had thought, it still gives me duplicates because the dates are different..

I'm trying to order by the date but only get the mw.mwv_minimum_wage_variation_id once..

open variation_cursor FOR
select distinct
mw.mwv_minimum_wage_variation_id,mw.prov_prov_id,TRUNC(mw.effective_date), mwv.minimum_wage_variation_id,mwv.english_text,mwv.french_text, wp.english_name, wp.french_name
from minimum_wage mw, minimum_wage_variation mwv, wid_province wp where mw.effective_date between TO_DATE(datefrom,'yyyy-mm-dd') and to_date(dateto,'yyyy-mm-dd')

and mw.mwv_minimum_wage_variation_id = mwv.minimum_wage_variation_id
and mw.prov_prov_id = wp.province_id
and mw.minimum_wage_ind = 1
and mw.mwv_minimum_wage_variation_id < 200

order by TRUNC(mw.effective_date);

the problem is this is what happens in the resultset:

8 41 12/1/1965 8 Rural areas
9 41 12/1/1965 9 Urban areas
8 41 7/1/1966 8 Rural areas
9 41 7/1/1966 9 Urban areas

I've tried group by but the same issue occurs.. I need to rewrite the query but am not sure how to proceed..

any thoughts welcome..

tks. Received on Wed Jul 26 2006 - 14:15:35 CDT