creating a view with min(datefield) ??

[Moshe Berlin]

how to create a view with a min(datefield) ?
The field names are all correct -
creation of the view without min() works fine,
but the creation of the view with min() gives the message "invalid
column".
select min(datefield) works, so the column is ok.

this is the view creation statement -
create view getfirstscores as
select
year,
category,
min(scoredate),
score
from scores

Thank you,
M. Berlin

[Andrey Chernyakhovsky]

> create view getfirstscores as
> select
> year,
> category,
> min(scoredate),
> score
> from scores

You need to group by all columns that are not aggregated.

[Moshe Berlin]

Hello Andrey, thanks-
OK, that is the normal way to select using aggregate functions, but I
got this error message - Is it asking for a name for a column to be
returned by the view? I haven't used views before, but now am starting.

>>Dynamic SQL Error
SQL error code = -607
Invalid command
must specify column name for view select expression
Statement: create view GetFirstScores as
select
yearcode,
divcode,
min(specdate) as first,
percent
from scores
group by
yearcode,
divcode,
percent

[Moshe Berlin]

Hello Andrey,
Got it, this works now.
the 'view columns' make it return the values found.
Thanks for the help.

create view getfirstscores (yc,dc,pc) as
select
yearcode,
divcode,
min(specdate),

percent
from scores
group by yearcode, divcode, percent