Re: simple sql question for those w/time.
From: joe_hamilton@my-dejanews.com
>
>Using Informix SQL..
>I cant seem to get my brain around this problem.
>I'm concerned with only four items in a large table' a customer sales
>database.
>Call them Cust, Price, Item_Description and Item_number (the Item_number
>being key for other tables)
>What I want to do is bring back a list of each customer (unique) with the
>Item_number and
>Item_Description of the most expensive (Price) item they have purchased.
>Simple no?
>I can bring back unique(cust) with max(price) alone and get the info I
>need.
>The problem is that I need the item_number and description included w/o
>pulling back _all_ the data in the table.
SELECT cust cno, MAX(price) maxprice
FROM table
GROUP BY 1
INTO TEMP t1;
SELECT cust, item_number, description, price
FROM table, t1
WHERE cust = cno
AND price = maxprice;
???
It's not perfect, but it's a start... :-)
HTH.
______________________________________________________
Get Your Private, Free Email at http://www.hotmail.com