Selecting Max from a sub-table? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Selecting Max from a sub-table?

Author

Topic

bluemetal
Starting Member

26 Posts

Posted - 2005-05-03 : 23:43:41

Hi, without worrying about too much of the query below. Can anyone of you help me to get results that would only contain the Highest version number (in the column pv.version)? Right now it is giving all the versions instead of just the highest.

Select P.Policy_Id, Pv.Policy_Number, Pa.Polacttyp_Code, Po.Poloption_Id, Pc.Pvcover_Id, Pv.Start_Datetime, O.Code,
Ob.Orgb_Id, Pv.Created_Date,
Pv.Effective_Date,
P.Cancel_Date,
Prc.Cover_Code,
-- Case When Prc.Cover_Code ='BUILD' Then Prc.Cover_Code Else '' End As Cov_Code,
Sum(Case When Pf.Feetype_Code ='FSL' Then Pf.Fee Else 0 End) As Fsl, Sum(Case When Pf.Feetype_Code ='SD' Then Pf.Fee Else 0 End) As Sd, Sum(Case When Pf.Feetype_Code ='GST' Then Pf.Fee Else 0 End) As Gst
, Max(Pv.Version)
From Policy P,
Policy_Version Pv,
Policy_Risk Pr,
Policy_Option Po,
Policy_Cover Pc,
Policy_Status Ps,
Policy_Action Pa,
Policy_Fee Pf,
Product_Cover_Type Pct,
Product_Cover Prc,
Organisation O,
Organisation_Branch Ob
Where P.Policy_Id = Pv.Policy_Id
And P.Policy_Id = Pv.Policy_Id
And Substr(Pv.Policy_Number,-1,1) = '8'
And P.Policy_Id = Pr.Policy_Id
And Pr.Polrisk_Id = Po.Polrisk_Id
And Po.Poloption_Id = Pc.Poloption_Id
And P.Polstatus_Code = Ps.Polstatus_Code
And P.Policy_Id = Pa.Policy_Id
And Pc.Poloption_Id = Pf.Poloption_Id
And Pc.Pvcover_Id = Pf.Pvcover_Id
And Po.Pvcovtype_Id = Pct.Pvcovtype_Id
And Pct.Pvcovtype_Id = Prc.Pvcovtype_Id
And Pc.Pvcover_Id = Prc.Pvcover_Id
And P.Org_Id = Pa.Org_Id
And P.Org_Id = O.Org_Id
And O.Org_Id = Ob.Org_Id
And P.Orgb_Id = Ob.Orgb_Id

Group By P.Policy_Id, Pv.Policy_Number, Pa.Polacttyp_Code, Po.Poloption_Id, Pc.Pvcover_Id, Pv.Start_Datetime, O.Code, Ob.Orgb_Id, Pv.Created_Date,
Pv.Effective_Date,
P.Cancel_Date , Prc.Cover_Code

Order By Cover_Code

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-05-03 : 23:57:48

If you don't want the MAX per set of joined unique values, then you will need a subquery in the FROM clause. Or, just set a variable to the MAX and use that for the column name instead.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

bluemetal
Starting Member

26 Posts

Posted - 2005-05-04 : 00:41:58

Thank You! I wonder why didn't i think of that! I think it happens when you're a month old at this job :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources