I have a table(product),It contain fields(productname,cost).
I want to retrieve the product name ,which cost is second
maximum in the table?
Answers were Sorted based on User's Feedback
Answer / venkat ramana
SELECT PRODUCTNAME
FROM PRODUCT
WHERE COST < (SELECT MAX(COST)FROM PRODUCT)
ORDER BY COST DESC
FETCH FIRST 1 ROW ONLY ;
HOPE THE ABOVE QUERY WILL PROVIDE YOU THE DESIRED RESULT.
Is This Answer Correct ? | 14 Yes | 2 No |
Answer / rajasekaran
with e as (select product_name,cost,rank() over(order by
cost desc) as rk from product) select * from e where rk=2;
Is This Answer Correct ? | 3 Yes | 0 No |
Answer / viki
SELECT PRODUCTNAME FROM PRODUCT WHERE COST <> max(COST)
AND PRODUCTNAME=
(SELECT PRODUCTNAME FROM PRODUCT WHERE COST =
(SELECT COST FROM PRODUCT
ORDER BY COST DESC
FETCH FIRST TWO ROWS ONLY))
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / dinesh m
A)
select productname from product
where cost=(Select max(cost) from product where cost not in
(select max (cost) from product) );
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / pc
select productname from product where cost=(select max(cost) from product where cost<> (select max(cost) from product));
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / vineeth varghese
(Select product from product where cost = (Select Max (cost) from product where cost not = (select max (cost) from product)))
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / siri
select productname from product p where 2=(select count(*) from product q where p.cost<=q.cost)
Is This Answer Correct ? | 0 Yes | 1 No |
Hi............
SELECT PRODUCTNAME FROM PRODUCT WHERE COST = (SELECT MAX(COST)
FROM PRODUCT WHERE COST > (SELECT MAX(COST) FROM PRODUCT));
THANKS
REGARDS
RAVINDRA BISHT
Is This Answer Correct ? | 13 Yes | 15 No |
Answer / ananth
We can also use correlated subquery for this..
Select product from product a where 1 =
(select count(*) from product b where a.cost < b.cost);
Is This Answer Correct ? | 0 Yes | 3 No |
Answer / pratap
A)Select product, Max (cost) from product where cost <
(select max (cost) from product);
Is This Answer Correct ? | 7 Yes | 13 No |
if any of the column names is provided wrong, where do you get the error(during precompilation or during Bind)
How many clustering indexes can be defined for a table?
how 2 resolve the -311 sqlcode
Why do chiropractors use drop table?
Where can you declare a cursor in a cobol-db2 program?
What is db2 isolation?
Name some fields from SQLCA.
What is filter factor?
What is db2?
What does DSNDB07 database do?
In SPUFI suppose you want to select maximum of 1000 rows, but the select returns only 200 rows. What are the 2 SQLCODEs that are returned?
What is buffer pool in the db2 database?