SQL - Supposea customer table ishaving different columns like customer no, payments.What will

GeekInterview.com > Interview Questions > Oracle > SQL

Go To First | Previous Question | Next Question

SQL | Question 21 of 171

Suppose a customer table is having different columns like customer no, payments.What will be the query to select top three max payments?

SELECT customer_no, payments from customer C1

WHERE 3<=(SELECT COUNT(*) from customer C2

WHERE C1.payment <= C2.payment)

Total Answers and Comments: 10

Last Update: October 28, 2009

Sponsored Links

Best Rated Answer

—

Submitted by: Ravi B

This is another simpler alternative:

SELECT customer_no, payments FROM
(SELECT customer_no, payments FROM
customer C1 ORDER BY payments DESC)
WHERE ROWNUM <4;

Above answer was rated as good by the following members:
bhatnagar_neeraj

March 30, 2005 12:29:44

Ravi B

RE: Suppose a customer table is having different columns like customer no, payments.What will ...

This is another simpler alternative:

SELECT customer_no payments FROM
(SELECT customer_no payments FROM
customer C1 ORDER BY payments DESC)
WHERE ROWNUM <4;

Is this answer useful? Yes | No

1 0

Overall Rating: +1

April 26, 2005 05:38:38

Manish Bhoge

RE: Suppose a customer table is having different columns like customer no, payments.What will ...

select distinct payments
from
customer A
where 5 > (
select count(*)
from customer B
where A.payments )
order by payments descnull

Is this answer useful? Yes | No

1 0

Overall Rating: +1

August 10, 2005 07:32:52

Jasjeet Singh

RE: Suppose a customer table is having different columns like customer no, payments.What will ...

select * from (select * from customer oder by payments asc)
where rownum<4;

Is this answer useful? Yes | No

0 1

Overall Rating: -1

May 18, 2006 07:06:25

nileshsingh

Member Since: May 2006 Contribution: 12

RE: Suppose a customer table is having diffe...

select ename sal from (select ename sal from <table_name> order by sal desc ) where rownum <4

Is this answer useful? Yes | No

1 0

Overall Rating: +1

July 25, 2007 02:42:38

tsourabh

Member Since: June 2007 Contribution: 1

RE: Suppose a customer table is having diffe...

select * from customer where rownum < 4 order by payment desc ;

Is this answer useful? Yes | No

January 30, 2008 03:09:49

patilpravin_1981

Member Since: December 2007 Contribution: 29

RE: Suppose a customer table is having different columns like customer no, payments.What will be the query to select top three max payments?

select customer_id payment from customer
having payment (select max(payment) from customer)
where rownum < 3

Is this answer useful? Yes | No

February 06, 2008 11:52:44

g_sidhu

Member Since: August 2007 Contribution: 122

RE: Suppose a customer table is having different columns like customer no, payments.What will be the query to select top three max payments?

SELECT ROWNUM as RANK Customer_no paymentsFROM (SELECT Customer_no payments
FROM customers ORDER BY payments DESC)WHERE ROWNUM < 3;

Is this answer useful? Yes | No

February 07, 2008 02:38:51

patilpravin_1981

Member Since: December 2007 Contribution: 29

RE: Suppose a customer table is having different columns like customer no, payments.What will be the query to select top three max payments?

Select customer_no max(payment) from customer
where rownum<4
group by customer_no;

Is this answer useful? Yes | No

August 18, 2009 11:33:00

ketansnadar

Member Since: August 2009 Contribution: 1

RE: Suppose a customer table is having different columns like customer no, payments.What will be the query to select top three max payments?

Situation 1 :

If the payments and the customer no are having single records then use this

SELECT top 3 * FROM Customer Order by Parments DESC

If the payments and the customer no are having more records and you want to sum up the payments and the retrieve then use this

SELECT TOP 3 [CustomerNo] SUM(Payments) FROM Customer group by [CustomerNo] order by sum(Payments) desc

Is this answer useful? Yes | No

October 28, 2009 02:41:07

#10

Rabindra_Kumar

Member Since: October 2009 Contribution: 6

RE: Suppose a customer table is having different columns like customer no, payments.What will be the query to select top three max payments?

SELECT * FROM (SELECT * FROM customer ORDER BY payment DESC)
WHERE rownum< 3

Is this answer useful? Yes | No

Go To Top

User Name		Remember Me?
Password

Question Bank