April 23, 2010 at 6:26 am
I have three tables [contractor], [estimate], [contract]. Here is what I have so far:
SELECT [Contract].ProjNumber, [Contract].Description, [Contractor].[Name], [Contractor].City,
MIN([Estimate].Estimate) as "Lowest Estimate"
FROM Contractor INNER JOIN
[Estimate] ON Contractor.ConNumber = [Estimate].ConNumber INNER JOIN
[Contract] ON Estimate.ProjNumber = [Contract].ProjNumber
GROUP BY
[Contract].ProjNumber, [Contract].Description, Contractor.[Name], Contractor.City
--SUBquery
SELECT Estimate.ProjNumber, MIN([Estimate].Estimate) as "Lowest Estimate"
FROM Estimate
GROUP BY Estimate.ProjNumber
HAVING MIN(Estimate.Estimate) > 1;
Now I can put the subquery in the where clause but all I get is the lowest overall estimate in the table and I need the lowest estimate for each contract.
April 23, 2010 at 7:42 am
tripower (4/23/2010)
I have three tables [contractor], [estimate], [contract]. Here is what I have so far:SELECT [Contract].ProjNumber, [Contract].Description, [Contractor].[Name], [Contractor].City,
MIN([Estimate].Estimate) as "Lowest Estimate"
FROM Contractor INNER JOIN
[Estimate] ON Contractor.ConNumber = [Estimate].ConNumber INNER JOIN
[Contract] ON Estimate.ProjNumber = [Contract].ProjNumber
GROUP BY
[Contract].ProjNumber, [Contract].Description, Contractor.[Name], Contractor.City
--SUBquery
SELECT Estimate.ProjNumber, MIN([Estimate].Estimate) as "Lowest Estimate"
FROM Estimate
GROUP BY Estimate.ProjNumber
HAVING MIN(Estimate.Estimate) > 1;
Now I can put the subquery in the where clause but all I get is the lowest overall estimate in the table and I need the lowest estimate for each contract.
You can change the JOIN criteria something like this:
SELECT...
FROM Contractor INNER JOIN
[Estimate] ON Contractor.ConNumber = [Estimate].ConNumber
and [Estimate].Estimate = (SELECT MIN(e.Estimate)
FROM [Estimate] e
WHERE e.ConNumber = Contractor.ConNumber
GROUP BY e.ProjNumber)
INNER JOIN...
Although, you might get better performance by using TOP 1 and an ORDER BY in the sub-select instead of an aggregate. It kind of depends on your indexing. You can also achieve the same effect using ROW_NUMBER() and on small data sets, it frequently performs even better than TOP, but it tails off as the data set sizes increase.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 23, 2010 at 7:47 am
To get faster response always put sample data with table/index definitions
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
April 23, 2010 at 8:53 am
Grant Fritchey (4/23/2010)
You can change the JOIN criteria something like this:
SELECT...FROM Contractor INNER JOIN
[Estimate] ON Contractor.ConNumber = [Estimate].ConNumber
and [Estimate].Estimate = (SELECT MIN(e.Estimate)
FROM [Estimate] e
WHERE e.ConNumber = Contractor.ConNumber
GROUP BY e.ProjNumber)
INNER JOIN...
Although, you might get better performance by using TOP 1 and an ORDER BY in the sub-select instead of an aggregate. It kind of depends on your indexing. You can also achieve the same effect using ROW_NUMBER() and on small data sets, it frequently performs even better than TOP, but it tails off as the data set sizes increase.
That doesn't bring back only the lowest bid. It brings back all records from the estimate table.
April 23, 2010 at 9:00 am
tripower (4/23/2010)
Grant Fritchey (4/23/2010)
You can change the JOIN criteria something like this:
SELECT...FROM Contractor INNER JOIN
[Estimate] ON Contractor.ConNumber = [Estimate].ConNumber
and [Estimate].Estimate = (SELECT MIN(e.Estimate)
FROM [Estimate] e
WHERE e.ConNumber = Contractor.ConNumber
GROUP BY e.ProjNumber)
INNER JOIN...
Although, you might get better performance by using TOP 1 and an ORDER BY in the sub-select instead of an aggregate. It kind of depends on your indexing. You can also achieve the same effect using ROW_NUMBER() and on small data sets, it frequently performs even better than TOP, but it tails off as the data set sizes increase.
That doesn't bring back only the lowest bid. It brings back all records from the estimate table.
You might need to adjust the WHERE clause but we use this type of query all the time to exactly what you're doing except we're going after the highest version number for a given value as opposed to the lowest number for a given value. The logic works.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 23, 2010 at 9:47 am
I don't know what to say it is bringing back all of the estimates.
April 23, 2010 at 9:59 am
tripower (4/23/2010)
I don't know what to say it is bringing back all of the estimates.
I'm not arguing, at all. I believe you.
You might try adjusting the WHERE cluase in the sub-select. I think I might have got it wrong. Instead of using the ConNumber, maybe you need to use the ProjNumber.
Like I say, the logic should work. You just have to adjust it to pull the data back in your system. Since I don't have your structure or data, I can't test, all I can do is make some suggestions based on the information provided.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 27, 2010 at 9:55 am
Bump.
April 27, 2010 at 12:16 pm
Something like this ?
SELECT [Contract].ProjNumber, [Contract].Description, [Contractor].[Name], [Contractor].City,
MIN([Estimate].Estimate) OVER(PARTITION BY Estimate.ProjNumber) as "Lowest Estimate"
FROM Contractor INNER JOIN
[Estimate] ON Contractor.ConNumber = [Estimate].ConNumber INNER JOIN
[Contract] ON Estimate.ProjNumber = [Contract].ProjNumber
GROUP BY
[Contract].ProjNumber, [Contract].Description, Contractor.[Name], Contractor.City
* Noel
April 27, 2010 at 12:54 pm
Since we're having troubles helping you out, I'd like to suggest that you provide us with the CREATE TABLE statements for your tables, INSERT statements for some sample data, and your expected results based upon the sample data that you provided. For information of how to easily do this, please read the article from the first link in my signature. Help us help you by providing this information in a way that makes it easy for us to just cut-and-paste it into SSMS and figure out what needs to be done.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply