• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 185
  • Last Modified:

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

0
scross1276
Asked:
scross1276
  • 4
  • 3
  • 2
  • +1
1 Solution
 
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
 
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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
 
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
 
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

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now