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

rakeshchhapare
Profile Answers by rakeshchhapare Questions by rakeshchhapare
Jan 15th, 2010
9
6989

Questions by rakeshchhapare

SQL

Answer

Showing Answers 1 - 9 of 9 Answers

aditi14
Profile Answers by aditi14

Feb 8th, 2010

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

KalimUllah
Profile Answers by KalimUllah

Mar 19th, 2010

SELECT TOP 5 OrderAmount, SalesPersonID FROM orders ORDER BY OrderAmount DESC

sunil_bisht
Profile Answers by sunil_bisht Questions by sunil_bisht

Mar 31st, 2010

You can fetch the Top 5 highest revenue transactions through another way
SELECT OrderAmount, SalesPersonID FROM (SELECT OrderAmount, SalesPersonID FROM order ORDER BY OrderAmout desc) WHERE rownum<=5;

ankitagujrati28
Profile Answers by ankitagujrati28

May 1st, 2010

select rownum,order_id
from (select order_id from orders
where rownum<=5
order by order_id desc);

ajitha_aj
Profile Answers by 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():

Raj_P
Profile Answers by Raj_P Questions by 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().

yagneswarasankar
Profile Answers by 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

krishsidd
Profile Answers by 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

kethinenisarath
Profile Answers by 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