Re: Difficulty to select distinct rows from view.

<andkovacs_at_yahoo.com> wrote in message
news:1118163086.681162.23640_at_g44g2000cwa.googlegroups.com...
> I have a view base on three columns( ID, historical_date, value)
> The view is built like this:
> select * from hourly_data
> Union
> select * from daily_data
> Union
> select * from weekly_data;
>
> I do the following select on this view:
>
> select ID, historic_date, value from hist_data;
>
> And it returns
>
> 10, 2005/05/04, 10.56
> 10, 2005/05/04, 10.53
>
> I only want the first row occur in the result set. Is there a way to do
> this ?
> How can I use DISTINCT option on columns (ID and historical_date ) ?
>

You should not use DISTINCT, just use GROUP BY with appropriate aggregate function.
Something like
select ID, historical_date, max(value) from (  select * from hourly_data
 Union All
 select * from daily_data
 Union All
 select * from weekly_data;
)
group by ID, historical_date

Dmitry. Received on Wed Jun 08 2005 - 01:13:50 CDT