Get Min Value (difficult for me, not you)

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

Transact-SQL (2000)

Get Min Value (difficult for me, not you)

Author

Topic

nic
Posting Yak Master

209 Posts

Posted - 2003-03-24 : 12:00:59

Hi,
I'm having trouble with this query and I'm running out of ideas. I need to select the primary key of a table with the minimum downpayPercent for each companyID. I created a sub query that returns a result of valid records, from this sub query I need to get the ID where the downpayPercent has the lowest value for each companyID. (Just the lowest value of the subquery because there could be lower values that don't fit the criteria).

SELECT
sub1.PaymentPlanID
FROM
(
SELECT
b.PaymentPlanID
,b.CompanyID
,b.DownPayPercent
FROM
Companies a INNER JOIN RatePaymentPlans b ON a.CompanyID = b.CompanyID
WHERE
a.state = 'UT'
AND b.EFT = 'N'
AND b.Term = '6M'
AND b.Renewal = 'New'
) AS Sub1
WHERE
Sub1.DownPayPercent = (MIN(sub1.DownPayPercent))
GROUP BY sub1.CompanyID

The Sub1 query works fine, it returns a number of valid records, now I need to get the PaymentPlanID with the minimum downpayPercent for each companyID. This query doesn't work but it kinda explains what I'm trying to accomplish.

I really appreciate any help or guidance.

Nic

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-03-24 : 12:18:43

Try this:

SELECT
sub1.PaymentPlanID
FROM
(
SELECT
b.PaymentPlanID
,b.CompanyID
,b.DownPayPercent
FROM
Companies a INNER JOIN RatePaymentPlans b ON a.CompanyID = b.CompanyID
WHERE
a.state = 'UT'
AND b.EFT = 'N'
AND b.Term = '6M'
AND b.Renewal = 'New'
) AS Sub1
WHERE
Sub1.DownPayPercent =
(SELECT MIN(DownPayPercent) FROM RatePaymentPlans r WHERE r.CompanyID = Sub1.CompanyID)
GROUP BY sub1.CompanyID

I think you can lose the subquery (Sub1) too, but to keep it clear I have left it as it is.

OS

nic
Posting Yak Master

209 Posts

Posted - 2003-03-24 : 12:27:36

Thanks for the help but since PaymentPlanID isn't in the subquery or group by statement it fails (the evil "not contained in either an aggregate function or the GROUP BY clause"). This why I'm running into trouble, I can't figure out how to get the just PaymentPlanID for the min(downpayPercent) for each companyID. I tried using "Having" instead of "Where" but still run into problems.

Thanks for the suggestion though

Nic

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-03-24 : 14:07:54

Here is a simpler version:

SELECT CompanyID, PaymentPlanID, DownPayPercent
FROM RatePaymentPlans p WHERE DownPayPercent =
(SELECT MIN(DownPayPercent) FROM RatePaymentPlans r
WHERE r.CompanyID = p.CompanyID)

Caveat: This could return more than one PaymentPlanID for companies that have two payment plans with the same (min) down percent

If this works adding the additional conditions shouldnt be much of a problem.

OS

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-03-24 : 14:18:25

I'd go with mohdowais's answer ... nice and short and to the point.

Just make sure you add the SAME conditions inside the WHERE subquery and in the main query, otherwise you will omit rows that should be returned.

- Jeff

nic
Posting Yak Master

209 Posts

Posted - 2003-03-24 : 15:02:10

Thanks but I'm not catching on. When I add the extra conditions things start falling apart. I simplified the table and query but can't get the proper result. Am I missing something???

CREATE TABLE [dbo].[RatePaymentPlans] (
[PaymentPlanID] [int],
[CompanyID] [int],
[DownPayPercent] [float],
[EFT] [varchar] (1),
) ON [PRIMARY]
GO

INSERT INTO RatePaymentPlans VALUES (1,1,100,'N')
go
INSERT INTO RatePaymentPlans VALUES (2,1,75,'N')
go
INSERT INTO RatePaymentPlans VALUES (3,1,33,'N')
go
INSERT INTO RatePaymentPlans VALUES (4,1,33,'Y')
go
INSERT INTO RatePaymentPlans VALUES (5,1,20,'Y')
go
INSERT INTO RatePaymentPlans VALUES (6,2,100,'N')
go
INSERT INTO RatePaymentPlans VALUES (7,2,50,'N')
go
INSERT INTO RatePaymentPlans VALUES (8,2,50,'Y')
go
INSERT INTO RatePaymentPlans VALUES (9,2,25,'N')
go
INSERT INTO RatePaymentPlans VALUES (10,2,25,'Y')
go

SELECT
a.PaymentPlanID
,a.CompanyID
,a.DownPayPercent
FROM
RatePaymentPlans a
WHERE
a.EFT = 'N'
AND a.DownPayPercent = (
SELECT MIN(a1.DownPayPercent)
FROM
RatePaymentPlans a1
WHERE
a1.EFT = 'N'
AND a1.PaymentPlanID = a.PaymentPlanID
)
go

The result should return 2 records paymentplanID equals 9 and 3. I don't see how to group by companyID or minimum value?

Nic

nic
Posting Yak Master

209 Posts

Posted - 2003-03-24 : 16:17:56

I'm dumb, thanks for your answers. I missed that the link bewteen the downpayPercent subquery and the main query was on CompanyID instead of paymentPlanID. your version works correctly.

Thanks for all your help and I apologise for being slow. ;)

Nic

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources