Produce a List of 5 Highest Revenue Transactions

You need to produce a list of the five highest revenue transactions from the Orders table in the Sales database. The Orders table is defined as follows:

CREATE TABLE Orders (
OrderID Int IDENTITY NOT NULL,
SalesPersonID Int NOT NULL,
RegionID Int NOT NULL,
OrderDate Datetime NOT NULL,
OrderAmount Int NOT NULL )

Which statement will produce the report correctly?



a. SELECT TOP 5 OrderAmount, SalesPersonID FROM orders
b. SELECT TOP 5 OrderAmount, SalesPersonID FROM orders ORDER BY OrderAmount DESC
c. SELECT TOP 5 WITH TIES OrderAmount, SalesPersonID From Orders
d. SELECT TOP 5 WITH TIES OrderAmount, SalesPersonID From Orders ORDER BY OrderAmount

Questions by rakeshchhapare

Showing Answers 1 - 27 of 27 Answers

aditi14

  • Feb 8th, 2010
 

SELECT salespersonid, orderamount FROM orders WHERE orderamount IN
(SELECT orderamount FROM orders ORDERBY orderamount DESC) WHERE rownum<=5

  Was this answer useful?  Yes

ajitha_aj

  • Jul 28th, 2010
 

From the answers to choose the following would be right I guess.


d. SELECT TOP 5 WITH TIES OrderAmount, SalesPersonID From Orders ORDER BY OrderAmount

The SQL would go like this i guess

SELECT OrderID, SalesPersonID, RegionID, OrderDate, OrderAmount,
   RANK() OVER
      (ORDER BY OrderAmount Desc NULLS LAST) AS Rank,
   DENSE_RANK() OVER
      (ORDER BY OrderAmount Desc NULLS LAST) AS Drank
   FROM Emp
   ORDER BY OrderAmount Desc NULLS LAST;

The difference between RANK() and DENSE_RANK() is that RANK() leaves gaps in the ranking sequence when there are ties. for example if 'A' and 'B' tie for second place lets say amount $1,500 and 'C' with $1000 as order amount would be in third place using DENSE_RANK() but only fourth place using RANK():

  Was this answer useful?  Yes

Raj_P

  • Sep 1st, 2010
 

Select  * From
(
       Select 

            A.*,

            Row_Number() Over (Order By OrderAmount Desc) Seq
       From
            OrderDetails A
)
Where Seq<=5

Note: We can also use "RANK() or DENSE_RANK()" function also in place of Row_NUmber().

  Was this answer useful?  Yes

yagneswarasankar

  • Nov 22nd, 2010
 

Both the queries should work .

 select * from (select last_name,salary,row_number() over(order by salary desc ) "Rank" from employees) where rownum < 6


select last_name,salary from (select last_name,salary,rownum rn  from employees order by salary desc) where rownum< 6

  Was this answer useful?  Yes

krishsidd

  • Nov 25th, 2010
 

insert into orders values(101,12301,11,sysdate,1000);
insert into orders values(102,12302,12,sysdate,500);
insert into orders values(103,12303,13,sysdate,1700);
insert into orders values(104,12304,14,sysdate,23100);
insert into orders values(105,12305,15,sysdate,3100);
insert into orders values(106,12306,16,sysdate,111);
insert into orders values(107,12307,17,sysdate,55);
insert into orders values(108,12308,18,sysdate,1001);
insert into orders values(109,12309,19,sysdate,10000);
insert into orders values(110,12310,20,sysdate,1900);
insert into orders values(111,12311,21,sysdate,1900);
insert into orders values(112,12312,22,sysdate,3100);


commit;


a. SELECT TOP 5 OrderAmount, SalesPersonID FROM orders
b. SELECT TOP 5 OrderAmount, SalesPersonID FROM orders ORDER BY OrderAmount DESC
c. SELECT TOP 5 WITH TIES OrderAmount, SalesPersonID From Orders
d. SELECT TOP 5 WITH TIES OrderAmount, SalesPersonID From Orders ORDER BY
OrderAmount


1) select orderamount,salespersonid, row_number() over(order by orderamount
desc) rank from (SELECT OrderAmount,SalesPersonID FROM orders order by
orderamount desc) y where rownum <= 5;


2) select orderamount,salespersonid, x rank from (SELECT
OrderAmount,SalesPersonID, rank() over(order by orderamount desc) x FROM orders)
y where x <=5;


3) select orderamount,salespersonid, x rank from (SELECT
OrderAmount,SalesPersonID, dense_rank() over(order by orderamount desc) x FROM
orders) y where x <=5;


For a,b - refer to 1
For c,d - can be done in 2 different ways as in 2 & 3

  Was this answer useful?  Yes

kethinenisarath

  • Mar 8th, 2011
 

A List of 5 Highest Revenue Transactions


select * from (SELECT OrderID,
SalesPersonID, RegionID, OrderDate, OrderAmount,
dense_rank()
over(partition by ORDERID
order by OrderAmount desc) "TOP Transactions"
from orders)
where "TOP Transactions"<=5;


or Find below query


select * from ( select ename,sal,empno,
dense_rank()
over(partition by deptno
order by sal desc) "top"
from emp)
where "top"<=5


Thanks.
kethinenisarath


  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions