Help with my select statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help with my select statement

Author

Topic

njohnson
Starting Member

3 Posts

Posted - 2007-08-30 : 14:48:08

I have developed an ASP.net web app using VWD 2005. This application is for a silent auction for our company. The database has only three tables: Auctions, Items, and Bids. My whole app works fine, I just need help with the select statement to end the auction. What I want to do is to go through the bids table and pull out the highest bid on each unique itemid. Here's what I got, but it's not exactly right:

select itemID, name, email, title, max(bid)
from vw_end
WHERE ([AuctionID] = @AuctionID)
group by ItemID, name, email, title

If I take out name, email, and title it gets the highest bid for each unique itemID. However, I need the name, email, and title of each selected records. Any help would be so greatly appreciated!

Kristen
Test

22859 Posts

Posted - 2007-08-30 : 14:52:14

Something like this:


select itemID, name, email, title, [MAX_bid]
from vw_end 
JOIN
(
    select [MAX_bid] = max(bid) 
    from vw_end 
    WHERE ([AuctionID] = @AuctionID) 
) AS X
     ON [AuctionID] = @AuctionID
    AND X.MAX_bid = bid
WHERE ([AuctionID] = @AuctionID)

Kristen

njohnson
Starting Member

3 Posts

Posted - 2007-08-30 : 15:33:51

Thanks for your help Kristen! However, when I try that it only returns one record which is the highest bid overall. I need the highest bid on each different itemid. Also, what does the X represent in the code you suggested?

Kristen
Test

22859 Posts

Posted - 2007-08-30 : 16:33:52

Hopefully! somethign like this then


select itemID, name, email, title, [MAX_bid]
from vw_end 
JOIN
(
    select [T_itemID] = itemID,
           [MAX_bid] = max(bid) 
    from vw_end 
    WHERE ([AuctionID] = @AuctionID) 
    GROUP BY itemID
) AS X
     ON [AuctionID] = @AuctionID
    AND X.MAX_bid = bid
    AND X.T_itemID = itemID
WHERE ([AuctionID] = @AuctionID)

Kristen

njohnson
Starting Member

3 Posts

Posted - 2007-08-30 : 16:55:46

AWESOME!! That was what I was trying to do! Thank you very much. Now all I need to do is study the code you gave me so I can understand exactly what is happening so I can possibly help someone else in the future. Thank you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources