How to convert query from t-sql to oracle sql?

Question

Oracle

Hello!
There are two tables:


You need to display a list of sellers and the number of their orders that have the maximum number of goods sold.

Query implemented in t-sql:

select top(1) with ties s.Name
     , sum(isnull(o.Amount, 0)) as Quantity
  from Sellers as s
 inner join Orders as o
    on s.id = o.Salesperson_id
 where o.Order_date is not null and o.Order_date > '20091231'
 group by s.id, s.Name
having count(o.Salesperson_id) > 1
 order by sum(isnull(o.Amount, 0)) desc
        , row_number() over(partition by s.id order by (select null))

Can you please tell me how to implement the same query in Oracle SQL Developer?

Answer 1

And what's the problem?

select s.Name
     , sum(nvl(o.Amount, 0)) as Quantity
  from Sellers as s,
          Orders as o
 where s.id = o.Salesperson_id 
   and o.Order_date is not null 
   and o.Order_date > to_date('20091231','YYYYMMDD') /*если это date*/
 group by s.id, s.Name
having count(o.Salesperson_id) > 1
 order by sum(nvl(o.Amount, 0)) desc
offset 0 rows fetch next 1 rows only

removed inner join since restrictions are imposed on Orders as o in where
, and this should work
. You can add this
too, row_number() over(partition by s.id order by null) but this does not guarantee the sort order, so you can omit it