CODEWITHSUNDEEP
oracle-database
acolls_badger
acolls_badger

Reputation: 465

Error on Partition over row number

I am creating a sub-query to select distinct entries on a certain column, DIS_COL, then return all other columns for those distinct entries, arbitrarily selecting the first row.

To do this I'm creating a sub-query that selects only first rows using over - partition by, then selecting from that sub-query.

There is an error with my code however; "ORA-00923: FROM keyword not found where expected".

My code is below:

select *
from (
   select *,
          row_number() over (partition by DIS_COL order by COL_2) as row_number --ORDER BY FIELD DETERMINES WHICH ROW IS THE FIRST ROW AND THUS WHICH ONE IS SELECTED.
   from MY_TABLE
   ) as rows
where row_number = 1
AND CRITERIA_COL = 'CRIT_1'
OR CRITERIA_COL_2 = 'CRIT_2';

How can I correct my code to achieve the desired result?

I am working on an Oracle database.

Upvotes: 0

Views: 1722

Answers (2)

Rusty
Rusty

Reputation: 2138

Remove as rows. It is not proper syntax for the table/query alias. It is syntax for column alias.

select *
from (
   select T.*, 
          row_number() over (partition by DIS_COL order by COL_2) as row_number --ORDER BY FIELD DETERMINES WHICH ROW IS THE FIRST ROW AND THUS WHICH ONE IS SELECTED.
   from MY_TABLE t
   ) 
where row_number = 1
AND (CRITERIA_COL = 'CRIT_1'
OR CRITERIA_COL_2 = 'CRIT_2');

Upvotes: 3

user4261009
user4261009

Reputation:

It's not the ROW_NUMBER, it's the *, Add an alias to the subquery:

select *
from (
   select T.*, -- here
          row_number() over (partition by DIS_COL order by COL_2) as row_number --ORDER BY FIELD DETERMINES WHICH ROW IS THE FIRST ROW AND THUS WHICH ONE IS SELECTED.
   from MY_TABLE
   )T as rows -- and here
where row_number = 1
AND CRITERIA_COL = 'CRIT_1'
OR CRITERIA_COL_2 = 'CRIT_2';

Upvotes: 0

Related Questions