RIck:
Thanks for the attempt ... after debugging, this is what I ran (same results
as before):
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, Max(MAX_BID) as ItemMaxBid,
(SELECT Max(MAX_BID)
FROM auc_bids
WHERE BIDDERS_ID = 3) AS MaxBid,
(SELECT Max(WINNING_BID)
FROM auc_bids
WHERE BIDDERS_ID = 3) AS CurrentBid
FROM auc_bids, auc_items
WHERE auc_bids.BIDDERS_ID = 3
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
Howard Owens
Internet Operations Coordinator
www.insidevc.com
[EMAIL PROTECTED]
AIM: GoCatGo1956
> -----Original Message-----
> From: [EMAIL PROTECTED] [SMTP:[EMAIL PROTECTED]]
> Sent: Monday, October 29, 2001 2:14 PM
> To: CF-Talk
> Subject: RE: (sql) Getting Unique Max() Value
>
> 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
