Reputation: 61
I have a sql query in db oracle I want to output max value. SQL query contains unique field values
Sql query below contains duplicate rows:
select
field1,field2,field3,field4,field5,field6,field7,field8,field9,MAX(field10) as field10
from(
select
field1,field2,field3,field4,field5,field6,field7,field8,field9,field10
from table
)tabl
GROUP BY field1,field2,field3,field4,field5,field6,field7,field8,field9
I corrected sql request but it doesn't work.
Sql query below an error message is displayed :ORA-00942 table or view does not exist.
select
field1,field2,field3,field4,field5,field6,field7,field8,field9
,(SELECT MAX(field10) FROM tabl ) as field10
from(
select
field1,field2,field3,field4,field5,field6,field7,field8,field9,field10
from table
)tabl
how can i fix it? Thanks for your help!
Upvotes: 0
Views: 1260
Reputation: 8111
First of all, your first query can not have duplicates, as you are grouping by all columns and using aggregate
function of remaining column, field10
.
Now coming back you your error. The reason for error is ,(SELECT MAX(field10) FROM tabl ) as field10
. Because tabl
is an alias for outer table and you are using it inside the select
clause, hence the error.
Also you can further simplify it like below and it will also not give you duplicates.
select
field1,field2,field3,field4,field5,field6,
field7,field8,field9,MAX(field10) as field10
from table
GROUP BY field1,field2,field3,field4,field5,field6,
field7,field8,field9
Upvotes: 2