Re: Difficulty to select distinct rows from view.

andkovacs_at_yahoo.com says...
> 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 ) ?

(a) the rows contain different values so DISTINCT will not help you. 
(b) UNION, rather than UNION ALL, applies an implicit DISTINCT to the set.
(c) define "first" - hourly_data takes precedence?

If assumption in (c) is correct then you will need to add WHERE clauses to your second and third selects to exclude values with column matches occurring in your earlier selects.

Geoff M Received on Tue Jun 07 2005 - 18:33:36 CDT