Trying to create a view with a Max

[sammybee77]

Hi All,

I'm trying to create the following view:-

create view blahblah
select max (id), field1, field2, field3
from table1
group by field1, field2, field3

When I try I get the following error:-

Error at line2
Ora-00998: must name this expression with a column alias

Where should I put my alias?

Many thanks

Sam

 

[ThomVF]

You need an alias for the max() function column. ORACLE cannot name a view column like this.

select max (id) maxidval , field1, field2, field3...


=================================
Thomas V. Flaherty Jr.
Birch Hill Technology Group, Inc.

http://www.birchhillgroup.com

 

[sammybee77]

Thank you for your help. My view is created but unfortunatley it is not selecting what I expected. I was hoping that I would get the maxid per field1, when I select all from my view I still get multiple rows per field1. ANy ideas?

Many thanks

Sam

 

[ThomVF]

Please provide a sample list of a few records and the results you hope to get so I can fully understand the situation... =================================
Thomas V. Flaherty Jr.
Birch Hill Technology Group, Inc.

http://www.birchhillgroup.com

 

[Turkbear]

Hi,
If there are multiple field2 values for a field1
value, then field1 will repeat for each of the
field2s..( same for multiple field3 relating to field2
and so on)
The group by creates 1 line for each UNIQUE combination of all the fields...
To prevent seeing the repeated data, issue a

BREAK ON field1 on field2 on field3

before running the query..

hth,