|
|||||||||
|
|||||||||
|
|||||||||
| |
|||
 |
|
|
|
|
|||||||||
|
|||||||||
|
|||||||||
| |
|||
|
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Display Modes |
Dev Articles Community Forums Sponsor:
|
|
|
|
#1
October 29th, 2002, 03:34 PM
|
|||
|
|||
|
Problem with SQL Statement
I am writing an ASP page to display the winners of an auction. Right now, I am getting all of the bids instead of the highest bid for each item.
Here is my SQL statement: Code:
strSQL = "SELECT Items.ItemID, Items.ItemName, Bids.BidderName, Bids.BidDate, Bids.BidTime, Max(Bids.BidAmount) AS CurrentBid FROM Items INNER JOIN Bids ON Items.ItemID = Bids.ItemID GROUP BY Items.ItemID, Items.ItemName, Bids.BidderName, Bids.BidDate, Bids.BidTime ORDER BY Items.ItemID " Can this be re-written so that only the winning bids are displayed or is that done in the body of the asp page? Thanks in advance.
|
|
#2
November 2nd, 2002, 03:21 AM
|
|||
|
|||
|
nested select
Your entire request can be done in 1 query, but you have to do some fancy footworking with SQL.
It can be achieved by using nesting select statement. In essence, you create a temporary table (tbl_1) which is created at run time, which stores by itemID de maximum price. Afterwards, you use this as input in your search in the bid table to find the rest of the record, and you use this as input for finding the itemname in item. Code:
SELECT
tbl_1.itemID,
b.BidderName,
b.BidDate,
b.BidTime,
tbl_1.MaxBid,
i.itemName
FROM (
(SELECT b.itemID, Max(b.BidAmount) as MaxBid
FROM bids b
GROUP by b.ItemID)
AS tbl_1
INNER JOIN Bids AS b ON
(tbl_1.MaxBid = b.BidAmount) AND
(tbl_1.itemID = b.ItemID))
INNER JOIN Item AS i ON
tbl_1.itemID = i.itemID
Good luck
__________________
- Rogier Doekes Last edited by rdoekes : November 2nd, 2002 at 08:26 AM.
|
|
#3
November 2nd, 2002, 01:08 PM
|
|||
|
|||
|
Thanks as always for your great advice!
Playing around with Access, I realized that I could create one query and then build that query into another to get the results I needed. I figured that a nested statement could do it, I just didn't know how to go about it. Now, thanks to you, I do! BTW, the auction went very well! The validation (that you had helped me on previously) worked great. Thanks to you and markerdave for all your assistance. Last edited by aspnewbie : November 2nd, 2002 at 01:22 PM.
|
|
| Viewing: Dev Articles Community Forums > Programming > ASP Development > Problem with SQL Statement |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
