Here's a quick stabb at it:
SELECT auc_bids.BIDS_ID,
auc_bids.ITEMS_ID,
auc_bids.BIDDERS_ID,
auc_bids.MAX_BID,
auc_bids.WINNING_BID,
auc_items.ITEMS_ID,
auc_items.ITEM_NUM,
auc_items.TITLE,
auc_items.DONOR, sum(auc_items.MAX_BID) as ItemMaxBid
(SELECT Max(MAX_BID)
FROM auc_bids
WHERE BIDDERS_ID = #session.BIDDERS_ID#) AS
MaxBid,
(SELECT Max(WINNING_BID)
FROM auc_bids
WHERE BIDDERS_ID =
#session.BIDDERS_ID#) AS CurrentBid
FROM auc_bids, auc_items
WHERE auc_bids.BIDDERS_ID = #session.BIDDERS_ID#
AND auc_bids.ITEMS_ID = auc_items.ITEMS_ID
GROUP BY auc_bids.BIDS_ID,
auc_bids.ITEMS_ID,
auc_bids.BIDDERS_ID,
auc_bids.MAX_BID,
auc_bids.WINNING_BID,
auc_items.ITEMS_ID,
auc_items.ITEM_NUM,
auc_items.TITLE,
auc_items.DONOR, MaxBid
Rick
-----Original Message-----
From: Owens, Howard [mailto:[EMAIL PROTECTED]]
Sent: Monday, October 29, 2001 4:49 PM
To: CF-Talk
Subject: (sql) Getting Unique Max() Value
I'm working on a charity auction application.
I have a page where I want to output all of a logged-in user's bids.
The page needs to show the item bid on, the current bid, the user's max bid.
Here's the SQL I've tried:
SELECT auc_bids.BIDS_ID,
auc_bids.ITEMS_ID,
auc_bids.BIDDERS_ID,
auc_bids.MAX_BID,
auc_bids.WINNING_BID,
auc_items.ITEMS_ID,
auc_items.ITEM_NUM,
auc_items.TITLE,
auc_items.DONOR,
(SELECT Max(MAX_BID)
FROM auc_bids
WHERE BIDDERS_ID = #session.BIDDERS_ID#) AS
MaxBid,
(SELECT Max(WINNING_BID)
FROM auc_bids
WHERE BIDDERS_ID =
#session.BIDDERS_ID#) AS CurrentBid
FROM auc_bids, auc_items
WHERE auc_bids.BIDDERS_ID = #session.BIDDERS_ID#
AND auc_bids.ITEMS_ID = auc_items.ITEMS_ID
The problem is, the MaxBid, for example, all come out with the highest bid
for all items bid on by this user. In other words, if the user bid $1,000
on Item A and $10 is his high bid on item B, Item B is still listed as
having a high bid of $1,000.
So I'm trying to figure out how to get a unique Max() value for each item as
a separate item.
H.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
