maximum amount of offer bidding

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

General SQL Server Forums

New to SQL Server Programming

maximum amount of offer bidding

Author

Topic

esambath
Yak Posting Veteran

89 Posts

Posted - 2008-07-30 : 12:23:51

Hi Friends

i need result for maximum amount of offer bidding amount.

For Example

Tbl_Bids
========

id ,vehicleID, offer, eAccountID
1 23 1500 1
2 44 2000 5
3 23 2200 7
4 23 2800 9

Tbl_eAccount
============

eAccountId, FirstName, LastName, Email
1 Smith Jop smith@yahoo.com
5 Johncy Jack Jack@redif.com
7 Diana Sam sam@yahoo.com
9 Jecy Jacoff Jacoff@yahoo.com

Tbl_Vehicle
===========

VehicleID, v_ModelID, TitleHolder, TypeAuction, Expiration
23 Yamaha LA single 30/7/2008 22:00:00
44 Ducati LXAA njk 31/7/2008 22:00:00

Expected output

Who is bid maximum amount for vehicle 23 is :

FirstName, Email, Maximum amount of offer, VehicleID, Expiration

Jecy , Jacoff@yahoo.com , 2800 , 23 , 30/7/2008 22:00:00

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-30 : 13:23:06

[code]SELECT a.FirstName,a.Email,
b.offer as [Maximum amount of offer],
v.VehicleID,
v.Expiration
FROM Tbl_Vehicle v
INNER JOIN Tbl_Bids b
ON b.vehicleID=v.VehicleID
INNER JOIN
(SELECT vehicleID,MAX(offer) AS MaxBid
FROM Tbl_Bids
GROUP BY vehicleID) mxbd
ON mxbd.vehicleID=b.vehicleID
AND mxbd.MaxBid=b.offer
INNER JOIN Tbl_eAccount a
ON a.eAccountId=b.eAccountId
WHERE v.VehicleID=23[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources