Another way is to use OLAP specification.
If you want to get only one row, use ROW_NUMBER().
If you want to get all rows having MAX(OrderPrice), use RANK() or DENSE_RANK().
Example 1: Returned one row. Added a row(o_id = 7).
Note 1: It may be better to use underscore("_") to separate words in a compoubd word, than use of initial capital letter.
Because, SQL is case insensitive.
Note 2: Date format 'yyyy/mm/dd' is not supported globally in DB2. It must be local format.
So, I changed your sample data to ISO format 'yyyy-mm-dd'.
Code:
------------------------------ Commands Entered ------------------------------
WITH Orders(O_Id , Order_Date , Order_Price , Customer) AS (
VALUES
( 1 , DATE('2008-11-12') , 1000 , 'Hansen' )
, ( 2 , DATE('2008-10-23') , 1600 , 'Nilsen' )
, ( 3 , DATE('2008-09-02') , 700 , 'Hansen' )
, ( 4 , DATE('2008-09-03') , 300 , 'Hansen' )
, ( 5 , DATE('2008-08-30') , 2000 , 'Jensen' )
, ( 6 , DATE('2008-10-04') , 100 , 'Nilsen' )
, ( 7 , DATE('2008-12-18') , 2000 , 'Nilsen' )
)
SELECT O_Id , Order_Date
, Order_Price AS Largest_Order_Price
, Customer
FROM (SELECT od.*
, ROW_NUMBER() OVER(ORDER BY Order_Price DESC) rnum
FROM Orders AS od
) od
WHERE rnum = 1
;
------------------------------------------------------------------------------
O_ID ORDER_DATE LARGEST_ORDER_PRICE CUSTOMER
----------- ---------- ------------------- --------
5 2008-08-30 2000 Jensen
1 record(s) selected.
Example 2: Returned two rows.
Code:
/* Same data as Example 1. */
SELECT O_Id , Order_Date
, Order_Price AS Largest_Order_Price
, Customer
FROM (SELECT od.*
, RANK() OVER(ORDER BY Order_Price DESC) rank
FROM Orders AS od
) od
WHERE rank = 1
;
------------------------------------------------------------------------------
O_ID ORDER_DATE LARGEST_ORDER_PRICE CUSTOMER
----------- ---------- ------------------- --------
5 2008-08-30 2000 Jensen
7 2008-12-18 2000 Nilsen
2 record(s) selected.
dBforums
>
SQL help
11-15-11, 13:01
Linear Mode
