MAX with multiple values. - SQL Server Forums

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

Transact-SQL (2005)

MAX with multiple values.

Author

Topic

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2008-06-17 : 09:22:36

Hi.
I;m trying to sort out some values.
Ok so for sort. We have a contract and a renewal column.
A contract can have many renewals.
I'm trying to get the latest renewal for each contract (latest renewal is the one with the higher number.

so im trying this..

select distinct c.policyno,c.renewalno
from   CONTRACT AS c inner join multicontract as d ON c.multicontractID = d.ID 
where
c.renewalno in (select max(crn.renewalno) from contract crn 
where 
 c.id= crn.id

But i keep getting multiple results (p.e. contra 12 ,renewal 13,14,15 and i want contract 12, renewal 15)
Any help?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-17 : 09:25:50

Did you try this?

select c.policyno,max(c.renewalno) as renewalno
from CONTRACT AS c inner join multicontract as d ON c.multicontractID = d.ID
group by c.policyno

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-17 : 09:25:55

[code]SELECT PolicyNo,
RenewalNo
FROM (
SELECT c.PolicyNo,
c.RenewalNo,
ROW_NUMBER() OVER (PARTITION BY c.PolicyNo ORDER BY c.RenewalNo DESC) AS RecID
FROM [Contract] AS c
INNER JOIN MultiContract AS d ON d.ID = c.MultiContractID
) AS d
WHERE RecID = 1[/code]

E 12°55'05.25"
N 56°04'39.16"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-06-17 : 09:26:30

select c.policyno, max(crn.renewalno) as renewalno
from CONTRACT AS c inner join multicontract as d ON c.multicontractID = d.ID
group by c.policyno

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-17 : 09:26:48

I hope RenewalNo is not VARCHAR! If so, "9" is later than "10000".

E 12°55'05.25"
N 56°04'39.16"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-06-17 : 09:27:28

x2

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2008-06-17 : 09:50:25

Hi.
Now it got worst. I give me more columns..
Ok i'll post all the query...
(not it's not varchar it's int)

select distinct c.policyno ,max(c.renewalno)as renewalno , d.assuredname,e.name1,f.address,f.streetnumber,f.zip as TK,city.description,county.description,
 IsNull((select sum(GrossTotal) from contractcoverinstalments where MULTIINSTALMENTSSCHID = a.id),0) as GrossTotal
FROM         dbo.MULTIINSTALMENTSSCH AS a INNER JOIN
                      dbo.INVOICES AS b ON a.ID = b.MULTIINSTALMENTSSCHID INNER JOIN
                      dbo.CONTRACT AS c ON b.CONTRACTID = c.ID INNER JOIN
                      dbo.MULTICONTRACT AS d ON c.MULTICONTRACTID = d.ID LEFT OUTER JOIN
                      dbo.DEFCAR AS Def ON Def.MULTICONTRACTID = d.ID --LEFT OUTER JOIN
--                      dbo.DEFCAR AS OldDef ON OldDef.ID = Def.LASTID 
INNER JOIN
                      dbo.PERSON AS e ON d.BROKERID = e.ID INNER JOIN
                      dbo.BRANCH ON c.BRANCHCODEID = dbo.BRANCH.CODEID LEFT OUTER JOIN
                      dbo.PERSONADDRESS AS f ON d.RECEIVERADDRESSID = f.ID LEFT OUTER JOIN
                      dbo.PERSONADDRESS AS f1 ON d.ASSUREDADDRESSID = f1.ID
right outer join
                      dbo.CITY ON Def.CITYID = dbo.CITY.ID 
 INNER JOIN
                      dbo.COUNTY ON dbo.CITY.COUNTYID = dbo.COUNTY.ID
where e.id in  (1,82388,85376)
and e.isbroker=1
and branch.codeid=19
-- and c.renewalno <>0
and exists ( select GrossTotal from contractcoverinstalments where MULTIINSTALMENTSSCHID = a.id and grosstotal <>0)
-- thes renewal date  > idiou simvolaiou date 
--and c.renewalno in (select max(crn.renewalno) from contract crn --INNER JOIN
                    --MULTICONTRACT AS dm ON crn.MULTICONTRACTID = dm.ID  
--where 
--crn.policyno in (select crn1.policyno from contract crn1 where crn1.id = crn.id)
--and c.id= crn.id
--and crn.category =3 

--)



group by a.id ,c.policyno ,c.renewalno , d.assuredname,e.name1,f.address,f.streetnumber,f.zip ,city.description,county.description
order by c.policyno

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-17 : 09:53:58

See my response 06/17/2008 : 09:25:55 on how to use ROW_NUMBER() function.

E 12°55'05.25"
N 56°04'39.16"

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2008-06-17 : 10:14:32

I'm trying but it's confusing.
where do all the declarations will go?
U use 2 from where do i put my original from?

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2008-06-17 : 10:18:35

I have this




select distinct c.policyno , d.assuredname,e.name1,f.address,f.streetnumber,f.zip as TK,city.description,county.description,
 IsNull((select sum(GrossTotal) from contractcoverinstalments where MULTIINSTALMENTSSCHID = a.id),0) as GrossTotal
FROM	(
		SELECT		c.PolicyNo,
				c.RenewalNo,
				ROW_NUMBER() OVER (PARTITION BY c.PolicyNo ORDER BY c.RenewalNo DESC) AS RecID
FROM         dbo.MULTIINSTALMENTSSCH AS a INNER JOIN
                      dbo.INVOICES AS b ON a.ID = b.MULTIINSTALMENTSSCHID INNER JOIN
                      dbo.CONTRACT AS c ON b.CONTRACTID = c.ID INNER JOIN
                      dbo.MULTICONTRACT AS d ON c.MULTICONTRACTID = d.ID LEFT OUTER JOIN
                      dbo.DEFCAR AS Def ON Def.MULTICONTRACTID = d.ID --LEFT OUTER JOIN
--                      dbo.DEFCAR AS OldDef ON OldDef.ID = Def.LASTID 
INNER JOIN
                      dbo.PERSON AS e ON d.BROKERID = e.ID INNER JOIN
                      dbo.BRANCH ON c.BRANCHCODEID = dbo.BRANCH.CODEID LEFT OUTER JOIN
                      dbo.PERSONADDRESS AS f ON d.RECEIVERADDRESSID = f.ID LEFT OUTER JOIN
                      dbo.PERSONADDRESS AS f1 ON d.ASSUREDADDRESSID = f1.ID
right outer join
                      dbo.CITY ON Def.CITYID = dbo.CITY.ID 
 INNER JOIN
                      dbo.COUNTY ON dbo.CITY.COUNTYID = dbo.COUNTY.ID) as d
where e.id in  (1,82388,85376)
and e.isbroker=1
and branch.codeid=19
-- and c.renewalno <>0
and exists ( select GrossTotal from contractcoverinstalments where MULTIINSTALMENTSSCHID = a.id and grosstotal <>0)
-- thes renewal date  > idiou simvolaiou date 
--and c.renewalno in (select max(crn.renewalno) from contract crn --INNER JOIN
                    --MULTICONTRACT AS dm ON crn.MULTICONTRACTID = dm.ID  
--where 
--crn.policyno in (select crn1.policyno from contract crn1 where crn1.id = crn.id)
--and c.id= crn.id
--and crn.category =3 

--)

is it correct?
It also says:
Msg 195, Level 15, State 10, Line 9
'ROW_NUMBER' is not a recognized function name.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-17 : 10:22:18

You must use SQL Server 2005 with COMPATIBILITY LEVEL set to 90.

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-17 : 10:24:16

Are you using SQL Server 2005?
You need to change the compatibility level to 90

Madhivanan

Failing to plan is Planning to fail

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2008-06-17 : 10:27:53

I cannot mess around with the sql setting because every change will effect the entire company. Thus i cannot change the compatibility level to 90. The sql was 2000 upgrated (god know how) to 2005 so i'm also not sure if it will work.
Is there another way? Through double selects on the max like i wasa trying to do?
Thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-17 : 10:28:10

[code]SELECT policyno,
renewalno,
assuredname,
name1,
address,
streetnumber,
TK,
citydescr,
countydescr,
GrossTotal
FROM (
SELECT c.policyno,
c.renewalno,
d.assuredname,
e.name1,
f.address,
f.streetnumber,
f.zip as TK,
city.description AS citydescr,
county.description as countrydescr,
yak.GrossTotal,
row_number() OVER (Partition by c.policyno order by c.renewalno desc) AS recid
FROM dbo.MULTIINSTALMENTSSCH AS a
INNER JOIN (
SELECT MULTIINSTALMENTSSCHID,
SUM(GrossTotal) AS GrossTotal
FROM contractcoverinstalments
GROUP BY MULTIINSTALMENTSSCHID
) AS Yak ON Yak.MULTIINSTALMENTSSCHID = a.id
AND yak.GrossTotal <> 0
INNER JOIN dbo.INVOICES AS b ON a.ID = b.MULTIINSTALMENTSSCHID
INNER JOIN dbo.CONTRACT AS c ON b.CONTRACTID = c.ID
INNER JOIN dbo.MULTICONTRACT AS d ON c.MULTICONTRACTID = d.ID
LEFT JOIN dbo.DEFCAR AS Def ON Def.MULTICONTRACTID = d.ID
INNER JOIN dbo.PERSON AS e ON d.BROKERID = e.ID
INNER JOIN dbo.BRANCH ON c.BRANCHCODEID = dbo.BRANCH.CODEID
LEFT JOIN dbo.PERSONADDRESS AS f ON d.RECEIVERADDRESSID = f.ID
LEFT JOIN dbo.PERSONADDRESS AS f1 ON d.ASSUREDADDRESSID = f1.ID
RIGHT join dbo.CITY ON Def.CITYID = dbo.CITY.ID
INNER JOIN dbo.COUNTY ON dbo.CITY.COUNTYID = dbo.COUNTY.ID
where e.id in (1, 82388, 85376)
and e.isbroker = 1
and branch.codeid = 19
) AS d
WHERE RecID = 1[/code]

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-17 : 10:30:46

[code]SELECT c.policyno,
c.renewalno,
d.assuredname,
e.name1,
f.address,
f.streetnumber,
f.zip as TK,
city.description AS citydescr,
county.description as countrydescr,
yak.GrossTotal
into #temp
FROM dbo.MULTIINSTALMENTSSCH AS a
INNER JOIN (
SELECT MULTIINSTALMENTSSCHID,
SUM(GrossTotal) AS GrossTotal
FROM contractcoverinstalments
GROUP BY MULTIINSTALMENTSSCHID
) AS Yak ON Yak.MULTIINSTALMENTSSCHID = a.id
AND yak.GrossTotal <> 0
INNER JOIN dbo.INVOICES AS b ON a.ID = b.MULTIINSTALMENTSSCHID
INNER JOIN dbo.CONTRACT AS c ON b.CONTRACTID = c.ID
INNER JOIN dbo.MULTICONTRACT AS d ON c.MULTICONTRACTID = d.ID
LEFT JOIN dbo.DEFCAR AS Def ON Def.MULTICONTRACTID = d.ID
INNER JOIN dbo.PERSON AS e ON d.BROKERID = e.ID
INNER JOIN dbo.BRANCH ON c.BRANCHCODEID = dbo.BRANCH.CODEID
LEFT JOIN dbo.PERSONADDRESS AS f ON d.RECEIVERADDRESSID = f.ID
LEFT JOIN dbo.PERSONADDRESS AS f1 ON d.ASSUREDADDRESSID = f1.ID
RIGHT join dbo.CITY ON Def.CITYID = dbo.CITY.ID
INNER JOIN dbo.COUNTY ON dbo.CITY.COUNTYID = dbo.COUNTY.ID
where e.id in (1, 82388, 85376)
and e.isbroker = 1
and branch.codeid = 19

SELECT w.policyno,
w.renewalno,
w.assuredname,
w.name1,
w.address,
w.streetnumber,
w.TK,
w.citydescr,
w.countrydescr,
w.GrossTotal
from #temp as w
inner join (
select policyno,
max(renewalno) AS renewalno
from #temp
group by policyno
) AS g on g.policyno = w.policyno
where w.renewalno = g.renewalno

drop table #temp[/code]

E 12°55'05.25"
N 56°04'39.16"

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2008-06-17 : 10:43:39

Hi.
Sorry my mistake i forgot to say that there is a switch c.category that gives 1=contract and 3= renewal and i don't want 2. So i added a c.category in (1,3) just beneath "and branch.codeid = 19' ....
It seems to work mate.....
Ok i'l check it again tomorrow and i'll tell you but from a quick look it seems to work fine....
Thanks all of you guys

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2008-06-18 : 05:32:49

As far i've checked it works.Thank you!

Any particular reason you did


FROM		dbo.MULTIINSTALMENTSSCH AS a
INNER JOIN	(
			SELECT		MULTIINSTALMENTSSCHID,
					SUM(GrossTotal) AS GrossTotal
			FROM		contractcoverinstalments
			GROUP BY	MULTIINSTALMENTSSCHID
		) AS Yak ON Yak.MULTIINSTALMENTSSCHID = a.id

intead of the isnull that i did? Or it's just another way.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources