simple sql question for those w/time.
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. Can someone point me in the right direction? thanks in advance. -J- -----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
On Mon, 03 May 1999 23:23:51 GMT, joe_hamilton@my-dejanews.com wrote:
>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.
select t1.cust, t1.item_number, t1.item_description, t1.price
from tbl_name t1
where price =
(select max(t2.price)
from tbl_name t2
where t1.cust=t2.cust)
group by 1,2,3,4
Hope that helps,
Douglas Wilson
Hi Joe,
try someting like this :
select Cust, Price, Item_Description, Item_number from csd test
where Price in (select max(price) from csd where cust=test.cust);
Dirk
joe_hamilton@my-dejanews.com schrieb:
> Using Informix SQL..
> I cant seem to get my brain around this problem.
>
> Im 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.
>
> Can someone point me in the right direction?
>
> thanks in advance.
> -J-
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own