How to convert query from t-sql to oracle sql?
Hello!
There are two tables:
Need to get a list of sellers and the number of orders that have the maximum number of goods sold.
T-sql implements the query:
Prompt how to implement the same query in Oracle SQL Developer?
There are two tables:
Need to get a list of sellers and the number of orders that have the maximum number of goods sold.
T-sql implements the query:
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))Prompt how to implement the same query in Oracle SQL Developer?
asked April 18th 20 at 13:07
1 answer
answered on
Solution
And what is the problem?
removed the inner join because the on Orders as o imposed restrictions in where
and it should work
And it is possible to add
, row_number() over(partition by s.id order by null) but it does not guarantee a sort order, so it can be omitted
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') /*if this is a 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 onlyremoved the inner join because the on Orders as o imposed restrictions in where
and it should work
And it is possible to add
, row_number() over(partition by s.id order by null) but it does not guarantee a sort order, so it can be omitted
Find more questions by tags OracleSQLTransact-SQL