Using CTE in Oracle SQL (ORA-00923: FROM keyword not found where expected)

Question

Question: from the following table containing a list of dates and items ordered, write a query to return the most frequent item ordered on each date. Return multiple items in the case of a tie.

Input for question in Oracle SQL

create table items(dates varchar2(200), item VARCHAR2(200));

insert into items values ('01-01-20', 'apple');
insert into items values ('01-01-20', 'apple');
insert into items values ('01-01-20', 'pear');
insert into items values ('01-01-20', 'pear');
insert into items values ('01-02-20', 'pear');
insert into items values ('01-02-20', 'pear');
insert into items values ('01-02-20', 'pear');
insert into items values ('01-02-20', 'orange');

This is the code I am trying to execute. However, I get an error

ORA-00923: FROM keyword not found where expected

Can someone please help?

select dates, item
from 
(
SELECT *, rank() OVER (PARTITION by dates ORDER BY item_count DESC) AS date_rank
FROM 
(
SELECT dates, item, count(*) AS item_count
FROM items
GROUP BY 1, 2
ORDER BY 1))
where date_rank=1;

Answer 1

I have replace * in your sub query with dates, items and changed group by clause from group by 1,2 to group by dates, items. It's working.

But I would suggest to use query#2 which is more readable in my opinion and easy to change in future.

 create table items(dates varchar2(200), item VARCHAR2(200));

 insert into items values ('01-01-20', 'apple');

 insert into items values ('01-01-20', 'apple');

 insert into items values ('01-01-20', 'pear');

 insert into items values ('01-01-20', 'pear');

 insert into items values ('01-02-20', 'pear');

 insert into items values ('01-02-20', 'pear');

 insert into items values ('01-02-20', 'pear');

 insert into items values ('01-02-20', 'orange');

Query#1:

select dates, item
     from 
     (
     SELECT dates,item, rank() OVER (PARTITION by dates ORDER BY item_count DESC) AS date_rank
     FROM 
     (
     SELECT dates, item, count(*) AS item_count
     FROM items
     GROUP BY dates, item
     ORDER BY dates) ) 
     where date_rank=1;

Output:

DATES	ITEM
01-01-20	apple
01-01-20	pear
01-02-20	pear

Query#2:

 with cte (dates,item,date_rank) as
 (  
    SELECT dates, item,rank() OVER (PARTITION by dates ORDER BY count(*) DESC)  AS date_rank
    FROM items
    GROUP BY dates, item
    ORDER BY dates
 )
 select  dates,item from cte 
 where date_rank=1;

Output:

DATES	ITEM
01-01-20	apple
01-01-20	pear
01-02-20	pear

db<fiddle here