SQL Query problem

Results 1 to 4 of 4

Thread: SQL Query problem

  1. #1
    kimberlyj Guest

    Default SQL Query problem

    Good Morning Everybody!<BR>I had a page that I thought was working but it is not working properly. I am building an auction site and I have a report page that is supposed to grab the auction number, current high bid, and the current high bidder. The first two work great, but I can&#039;t seem to pull the proper bidder, it just picks one at random. Here is my code for the auction number, and current high bid, and bidder. Bidder is rs3 and doesn&#039;t work properly.<BR><BR>set rs1= conn.Execute("Select AucCode as Expr1, AucClose from AucLots ORDER BY AucClose DESC")<BR>Do While Not rs1.EOF<BR>set rs2=conn.Execute("Select MAX(BAmount) as High from AucBids Where BAucCode=" & "&#039;" & rs1.Fields("Expr1").value & "&#039;")<BR>set rs3=conn.Execute("Select Bidder as Code from AucBids Where BAucCode="& "&#039;" & rs1.Fields("Expr1").value & "&#039;")<BR>rs1.Movenext<BR>Loop<BR>Any help is appreciated!<BR>Thanks,<BR>Kimberly<BR>

  2. #2
    Join Date
    Dec 1969

    Default RE: SQL Query problem

    Try this one SQL in one RS (instead of rs2 and rs3),<BR><BR>select BAmount, Bidder from AucBids <BR>where BAmount = (select max(BAmount) from AucBids)

  3. #3
    Join Date
    Dec 1969

    Default You need to

    have some sort of join information that matches the highest bid (using the bidders id) to the bidder info table. Looking at your code you&#039;ll just get one of the (probably the highest id) from the bidder info table that is in the auction of interest.<BR><BR>Seems to me that you need to do something like<BR><BR>Select a join on auction table with bidamount table, selecting the max bidamount.<BR><BR>Then for each record in that recordset run a query that joins bidamount to bidder info where auctionid = first recordset auctionid and bidamount = first recordset bid amount. There must be a better way of getting this info all in one shot though, some db redesign and thought would be necessary, possibly asign a maxbid field to true for a new max bid and assign all other maxbids to false for a given auction.Then need only one query that joins all tables just select only max bids.

  4. #4
    kimberlyj Guest

    Default works great!

    Thanks so much! Wish I had thought of it!

Posting Permissions

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