SQL Query Help Needed (max)

I have three tables that I need data from based on the maximum bid amount from the bids table for each item found in the auctions table.  I can't get my query to only return the winning bidder.  It keeps returning everyone who bid on each item.


select a.name, max(b.bid), e.first_name, e.last_name
from auctions a
	inner join bids b on b.auction_id = a.id
	inner join employees e on e.employee_id = b.employee_id
where b.submitted > '2011-09-20'
group by a.name, e.first_name, e.last_name

Open in new window

scross1276Asked:
Who is Participating?
 
Rajkumar GsSoftware EngineerCommented:
select ac.name, a.highbid, e.first_name, e.last_name from 
(
	select auction_id, MAX(bid) highbid from bids
	group by auction_id
) a
inner join bids b on a.auction_id = b.auction_id and a.highbid = b.bid
inner join employees e on e.employee_id = b.employee_id 
inner join auctions ac on b.auction_id = ac.id

Open in new window


Raj
0
 
Ephraim WangoyaCommented:
try
select top 1 *
from
(
select a.name, max(b.bid) [High Bid], e.first_name, e.last_name
from auctions a
	inner join bids b on b.auction_id = a.id
	inner join employees e on e.employee_id = b.employee_id
where b.submitted > '2011-09-20'
group by a.name, e.first_name, e.last_name
) A
order by A.[High Bid] desc

Open in new window

0
 
scross1276Author Commented:
ewangoya, that only gives me the highest bid of all of them.

I should have been more descriptive.  There are 108 auctions, many with multiple bids, that I need to know the highest bidder for.  This query returns the highest overall bid from all 108 auctions.
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
Ephraim WangoyaCommented:
try
select C.name, C.[High Bid], e.first_name, e.last_name
from employees e
inner join (select a.name, max(b.bid) [High Bid], b.employee_id
            from auctions a
	        inner join bids b on b.auction_id = a.id
	        where b.submitted > '2011-09-20'
	        group by a.name
	        ) C on e.employee_id = C.employee_id
inner join bids d on d.bid = c.[High Bid]

Open in new window

0
 
scross1276Author Commented:
That returns over 19,000 records...  :)

Way over the 108 I need.
0
 
scross1276Author Commented:
This query returns the right number of rows, but jumps up to over 300 when I add the employees table to it to get the winning bidders name.  Any ideas on that?
select max(b.bid) [Winning Bid], b.auction_id, a.name
from bids b
	left outer join auctions a on a.id = b.auction_id
where submitted > '2011-09-20'
group by auction_id, a.name

Open in new window

0
 
scross1276Author Commented:
Perfect!!! Thank you so much!
0
 
Rajkumar GsSoftware EngineerCommented:
Including date check
select ac.name, a.highbid, e.first_name, e.last_name from 
(
	select auction_id, MAX(bid) highbid from #bids
	where submitted > '2011-09-20'
	group by auction_id
) a
inner join #bids b on a.auction_id = b.auction_id and a.highbid = b.bid
inner join #employees e on e.employee_id = b.employee_id 
inner join #auctions ac on b.auction_id = ac.id
where b.submitted > '2011-09-20'

Open in new window

0
 
Rajkumar GsSoftware EngineerCommented:
Glad I could help!

Remember to use my final query - http:#36980657 - which contains date check as wel

Raj
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you might want to read this article also to know other options:
http://www.experts-exchange.com/A_3203.html
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.