Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2003
    Posts
    52

    Unanswered: help in MAX aggregate function, plz, urgent

    hi, i want to get userIds and max bids of bidders who have proxy bids (automated bidding) which are more than the current bid. and it should not include the user id from the last bid(or current bid).

    so in simple words wat i need to get is all maxBids wich are more than the last bid made and i shuould get the maximumBid if a user has two entried of maxBids.

    the rough query is somewat like
    select t_bid.uId,t_bid.maxBid from t_bid where t_bid.auctionId = 7 and t_bid.maxBid > = t_bid.currBid and t_bid.uId <> 2 order by t_bid.bidDate

    the underlined part should be like max(t_bid.currBid) and the other underline part t_bid.uId <> 2 is the userid with currentBid or u can say last bid. i ned to get the last bid uId.

    one more thing is it better to get them in some group by thing? just asking

    the table is
    Code:
    bidId	uId	auctionId	currBid	maxBid	bidDate
    3	7	7	155100	155100	12/5/2003 3:31:51 AM
    6	8	7	160100	170000	12/5/2003 4:27:03 AM
    7	2	7	165000	165000	12/5/2003 5:37:29 AM
    so if we consider a case where

    Code:
    bidId	uId	auctionId	currBid	maxBid	bidDate
    3	7	7	155100	155100	12/5/2003 3:31:51 AM
    6	8	7	160100	170000	12/5/2003 4:27:03 AM
    7	2	7	165000	165000	12/5/2003 5:37:29 AM
    8	8	7	168000	180000	12/5/2003 5:37:29 AM
    9	11	7	169000	169000	12/5/2003 5:37:29 AM
    in this case i don't need two redundant records of uId=8 but the latest one with biggerr maxBid which 180000

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You didn't like this answer?

    http://www.dbforums.com/t972794.html

    Why not?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jan 2003
    Posts
    52

    Re: help in MAX aggregate function, plz, urgent

    sorry abt dat, i totally forgot the sol u gave me, thanx again
    but i need to get that part actaully how to skip this last entry. and getting only one maxBid if same user have bid twice with 2 maxBid and gettign the bigger one.
    Originally posted by waqas

    and the other underline part t_bid.uId <> 2 is the userid with currentBid or u can say last bid. i ned to get the last bid uId.

    the table is
    Code:
    bidId	uId	auctionId	currBid	maxBid	bidDate
    3	7	7	155100	155100	12/5/2003 3:31:51 AM
    6	8	7	160100	170000	12/5/2003 4:27:03 AM
    7	2	7	165000	165000	12/5/2003 5:37:29 AM
    so if we consider a case where

    Code:
    bidId	uId	auctionId	currBid	maxBid	bidDate
    3	7	7	155100	155100	12/5/2003 3:31:51 AM
    6	8	7	160100	170000	12/5/2003 4:27:03 AM
    7	2	7	165000	165000	12/5/2003 5:37:29 AM
    8	8	7	168000	180000	12/5/2003 5:37:29 AM
    9	11	7	169000	169000	12/5/2003 5:37:29 AM
    in this case i don't need two redundant records of uId=8 but the latest one with biggerr maxBid which 180000

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Try my code, it will only return you the one row...

    SELECT *
    FROM xt_bid o
    WHERE bidPrice IN (SELECT MAX(BidPrice)
    FROM xt_bid i
    WHERE auctionId = 1)

    you'll Just find a problem with duplicate max bids..

    That would be a tie...what would you do then?

    Please try the code and see if it works for you..
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Nov 2003
    Posts
    48
    An extra condition will help you get the maxBid for each user

    for the query you gave,

    Code:
    select t_bid.uId,t_bid.maxBid 
    from t_bid 
    where t_bid.auctionId = 7 
      and t_bid.maxBid > = t_bid.currBid 
      and t_bid.uId <> 2 
    order by t_bid.bidDate
    It can be changed to

    Code:
    select t_bid.uId,t_bid.maxBid 
    from t_bid 
    where t_bid.auctionId = 7 
      and t_bid.maxBid > = t_bid.currBid 
      and t_bid.uId <> 2 
      and t_bid.maxBid in (select max(currBid) from t_bid where uId = t_tid.uId)
    order by t_bid.bidDate
    Shianmiin

  6. #6
    Join Date
    Jan 2003
    Posts
    52
    look heres my query
    Code:
    select temp1.uId,maxBid,bidDate
    from temp1 
    where temp1.auctionId = 7 
    	and temp1.uId <> 2 i don't want last bidder, how to discard last entry, wat should be this query, how to get last bidder 
    	and temp1.maxBid > = (select max(currBid) from temp1 where auctionId=7) 
    order by bidDate
    heres the result
    Code:
    uId	maxBid	                bidDate
    8	180000.0000	2003-12-05 04:27:03.000-----X smaller bid of same uId
    8	190000.0000	2003-12-05 05:39:29.000
    1	175000.0000	2003-12-05 05:44:29.000-----X latest/last bidder
    i don't want the rows with X

  7. #7
    Join Date
    Nov 2003
    Posts
    48
    oh I see, try this, it should work.

    Code:
    select
       a.uId, a.maxBid, a.bidDate
    from
       temp1 a,
       (select uId, currBid 
        from temp1
        where actionId = 7
          and bidId in (select max(bidId) from temp1)
       ) b
    where a.actionId = 7 
      and a.uId <> b.uId
      and a.maxBid >= b.currBid
      and a.maxBid in (select max(currBid) from temp1 where uId = a.uId)
    Last edited by shianmiin; 12-06-03 at 09:35.
    Shianmiin

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •