Help with SQL statement, Please!

From: Newbie \(Joe \) <"Newbie>
Date: 2000/06/30
Message-ID: <Xn275.335$Hs3.7506_at_news1.mts.net>#1/1


Hello,

    I want to select rows from a table where only one field is unique...ie:A customer may have several purchases, but I want to return only the biggest purchases per customer.

    I currently have it returning all purchases for every customer, but would like to see only the max value(of actual_price) per customer(determined by 'pin'). Here's what I have:

SELECT purchases.date_purchased, products.name as product_name, states.name as state_name,
customers.first_name ||' '|| customers.last_name as customer_name, purchases.product_cost, purchases.adjusted_price, FROM purchases, products, states, customers WHERE purchases.product_id = products.product_id AND purchases.pin = customers.pin
AND states.state_code = purchases.state_purchased_in AND states.state_code IN ( SELECT state_code FROM states
WHERE region_id = in_region )
ORDER BY purchases.actual_price DESC
 ;

How can I ensure that there is only one value returned for each customer(pin)?
Thanks in advance!!
Joe

--
Received on Fri Jun 30 2000 - 00:00:00 CEST

Original text of this message