Click here to Skip to main content
15,851,387 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Currently, the query output is; There are 2 items with the highest price: $80.00

How can I show the partids for the highest price in the program below as well.?

I would like the output to be: There are 2 items with the highest price: $80.00 with partids(This should be the partids of the highest prices).


DECLARE

vPrice inventory.price%TYPE;
vPartid inventory.partid%TYPE;
vcnt INTEGER;

CURSOR highestprice_cur
IS
SELECT price, vcnt,vpartid
--INTO vprice, vcnt

FROM (SELECT price, COUNT(*) vcnt
FROM inventory
GROUP BY price
ORDER BY price DESC)
WHERE ROWNUM =1;
highestprice_items_cur highestprice_cur%ROWTYPE;
BEGIN
OPEN highestprice_cur;
LOOP
FETCH highestprice_cur INTO vprice, vcnt,vpartid;
EXIT
WHEN highestprice_cur%NOTFOUND;
IF vcnt > 1
THEN
DBMS_OUTPUT.put_line(
'There are '
|| vcnt || ' items with the highest price: '
|| TO_CHAR(vprice, 'fm$999,99.00'));
END IF;
END LOOP;
CLOSE highestprice_cur;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line('ERROR OTHERS ' || SQLERRM);
END;
Posted
Updated 13-Feb-13 19:34pm
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900