results for highest row/field - 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 2008 Forums

Transact-SQL (2008)

results for highest row/field

Author

Topic

Ratz03
Starting Member

27 Posts

Posted - 2015-01-26 : 11:52:10

Hi All,

I am writing a query to pull out fields from a table for the highest version number for each document id.

source table
version document id
7647178 7647178
7713239 7647178
7713272 7647178
7647178 7647179
7713279 7647179
7713280 7647179

my output should be
version document id
7713272 7647178
7713280 7647179

Thanks for the help in advance

Ifor
Aged Yak Warrior

700 Posts

Posted - 2015-01-26 : 12:20:10

[code]
SELECT MAX([version]) AS [version]
,document_id
FROM YourTable
GROUP BY document_id;
[/code]

Ratz03
Starting Member

27 Posts

Posted - 2015-01-27 : 07:45:26

I tried to put in the logic for max, it did not work. Please see my original query. This query returns multiple rows, I want the rows with highest version_ref for each policy_band_ref.

SELECT [dbo].[fnPolicyNumberLogic](A.insurer_policy_no,A.effective_date_key,DENSE_RANK() OVER (PARTITION BY A.[insurer_policy_no] ORDER BY A.[policy_band_ref])) AS FinalPolicyRefernce
,A.[insurer_policy_no]
,A.[effective_date_key]
,A.[term_end_date_key]
,A.[accepted_date_key]
,A.[product_name]
,B.[employer_paye_no]
,A.[policy_holder_name]
,A.[address_line_1]
,A.[address_line_2]
,A.[address_line_3]
,A.[City]
,A.[County]
,A.[Country]
,A.[Postcode]
,A.[revised_annual_premium]
,A.[transaction_premium]
,B.[subsidiary_name]
,A.[policy_status_key]
,A.[version_ref]
,A.[policy_band_ref]
,A.[veh_reg_no]
,A.[policy_premium]
,A.[policy_commission]
,A.[policy_ipt]
,C.IsError
FROM dbo.[ActurisDW_Policy_Main] A
LEFT JOIN dbo.ActurisDW_Policy_Subs B
ON A.insurer_policy_no = B.Insurer_Policy_no
Left Join ( select distinct policy_ID, isError from (
select policy_id, Case when status = 4 THEN 'Y' ELSE 'N' end as isError from IntDB_Policy ) a where isError = 'Y' ) C
ON A.policy_band_ref = C.policy_id

sz1
Aged Yak Warrior

555 Posts

Posted - 2015-01-27 : 11:16:35

With getmax
As
(
SELECT [dbo].[fnPolicyNumberLogic](A.insurer_policy_no,A.effective_date_key,
DENSE_RANK() OVER (PARTITION BY A.[insurer_policy_no] ORDER BY A.[policy_band_ref])) AS FinalPolicyRefernce
,A.[insurer_policy_no]
,A.[effective_date_key]
,A.[term_end_date_key]
,A.[accepted_date_key]
,A.[product_name]
,B.[employer_paye_no]
,A.[policy_holder_name]
,A.[address_line_1]
,A.[address_line_2]
,A.[address_line_3]
,A.[City]
,A.[County]
,A.[Country]
,A.[Postcode]
,A.[revised_annual_premium]
,A.[transaction_premium]
,B.[subsidiary_name]
,A.[policy_status_key]
,A.[version_ref]
,A.[policy_band_ref]
,A.[veh_reg_no]
,A.[policy_premium]
,A.[policy_commission]
,A.[policy_ipt]
,C.IsError

FROM dbo.[ActurisDW_Policy_Main] A
LEFT JOIN dbo.ActurisDW_Policy_Subs B
ON A.insurer_policy_no = B.Insurer_Policy_no
Left Join ( select distinct policy_ID, isError from (
select policy_id, Case when status = 4 THEN 'Y' ELSE 'N' end as isError
from IntDB_Policy ) a where isError = 'Y' ) C
ON A.policy_band_ref = C.policy_id
)
Select policy_band_ref PolicyRef, Max(Version_ref) HighestVersion
From getmax

Group By policy_band_ref
Order By policy_band_ref ASC

We are the creators of our own reality!

Ratz03
Starting Member

27 Posts

Posted - 2015-01-27 : 11:50:59

Thanks SZ1. The query runs without errors but does not give desired results.

See output below from query.
PolicyRef HighestVersion
24214891 24214891
24214891 26147927
24331326 24331326
24331326 25519980

My desired results:
1. For each policy ref only the highest version, The query is returning 2 highest versions for each policyref.
2. All original columns along with the highest version.

sz1
Aged Yak Warrior

555 Posts

Posted - 2015-01-27 : 12:02:20

Try adding the line below after the Group By, you could also use row_number to get the distinct PolicyRef

Having Count(FinalPolicyRefernce) <2

We are the creators of our own reality!

Ratz03
Starting Member

27 Posts

Posted - 2015-01-28 : 09:19:12

this does not work :(

the query still does not return one row per policy as there are other columns where values are different.

sz1
Aged Yak Warrior

555 Posts

Posted - 2015-01-28 : 09:49:27

Try this, I dont have any data to test.

With getmax
As
(
SELECT [dbo].[fnPolicyNumberLogic](A.insurer_policy_no,A.effective_date_key,
DENSE_RANK() OVER (PARTITION BY A.[insurer_policy_no] ORDER BY A.[policy_band_ref])) AS FinalPolicyRefernce,
ROW_NUMBER() over(Partition by policy_band_ref
order by Version_ref desc) seq
,A.[insurer_policy_no]
,A.[effective_date_key]
,A.[term_end_date_key]
,A.[accepted_date_key]
,A.[product_name]
,B.[employer_paye_no]
,A.[policy_holder_name]
,A.[address_line_1]
,A.[address_line_2]
,A.[address_line_3]
,A.[City]
,A.[County]
,A.[Country]
,A.[Postcode]
,A.[revised_annual_premium]
,A.[transaction_premium]
,B.[subsidiary_name]
,A.[policy_status_key]
,A.[version_ref]
,A.[policy_band_ref]
,A.[veh_reg_no]
,A.[policy_premium]
,A.[policy_commission]
,A.[policy_ipt]
,C.IsError

FROM dbo.[ActurisDW_Policy_Main] A
LEFT JOIN dbo.ActurisDW_Policy_Subs B
ON A.insurer_policy_no = B.Insurer_Policy_no
Left Join ( select distinct policy_ID, isError from (
select policy_id, Case when status = 4 THEN 'Y' ELSE 'N' end as isError
from IntDB_Policy ) a where isError = 'Y' ) C
ON A.policy_band_ref = C.policy_id
)
Select policy_band_ref PolicyRef, Max(Version_ref) HighestVersion
From getmax
Where seq = 1
Group By policy_band_ref
Order By policy_band_ref ASC

We are the creators of our own reality!

sz1
Aged Yak Warrior

555 Posts

Posted - 2015-01-28 : 10:18:08

The above should work, here is a small test script to show the logic working.

Create Table #MyList
(
PolicyID int Primary Key Not null Identity (1,1),
PolicyRef int null,
VersionRef int null
)

Insert Into #MyList
Values
(7647178, 7647181) ,
(7713239, 7647178),
(7713272, 7647178),
(7647178, 7647179),
(7713279, 7647179),
(7713280, 7647179),
(7713281, 7647180)

select * from #MyList -- check for duplicates

With getmax -- get only last version number, note 7647178 appears once
As
(
SELECT
DENSE_RANK() OVER (PARTITION BY policyref ORDER BY Versionref desc)AS FinalPolicyRefernce,
ROW_NUMBER() over(Partition by policyref order by versionref desc) seq, -- create numbering to get last version desc
policyref, versionref

FROM #MyList

)
Select policyref PolicyRef, Max(Versionref) HighestVersion
From getmax
Where seq = 1 -- get last record
Group By policyref
Order By policyref ASC

We are the creators of our own reality!

jleitao
Posting Yak Master

100 Posts

Posted - 2015-01-28 : 16:17:02

In the first sz1 query remove the column "Version_ref" from the group by (and the "distinct").

With getmax
As
(
SELECT
...
...
)

Select policy_band_ref, Max(Version_ref) HighestVersion
From getmax
Group By policy_band_ref
Order By policy_band_ref ASC

------------------------
PS - Sorry my bad english

sz1
Aged Yak Warrior

555 Posts

Posted - 2015-01-29 : 04:25:25

Yes you can try that as its grouping there will give you the dups. Also take note of other query for ref which works.

We are the creators of our own reality!

Ratz03
Starting Member

27 Posts

Posted - 2015-01-29 : 07:16:30

Thanks everyone for your help. I have got the query to be working by putting in nested queries like this.

SELECT *
FROM (
SELECT DISTINCT
[dbo].[fnPolicyNumberLogic](insurer_policy_no,effective_date_key,DENSE_RANK() OVER (PARTITION BY [insurer_policy_no] ORDER BY [policy_band_ref])) AS FinalPolicyRefernce
,A.[insurer_policy_no]
,A.[version_reference]
,A.[effective_date_key]
,A.[term_end_date_key]
,A.[accepted_date_key]zas
,A.[product_name]
,A.[ern_no]
,A.[policy_holder_name]
,A.[address_line_1]
,A.[address_line_2]
,A.[address_line_3]
,A.[City]
,A.[County]
,A.[Country]
,A.[Postcode]
,A.[revised_annual_premium]
,A.[transaction_premium]
,A.[subsidiary_name]
,A.[policy_status_key]
,A.[policy_band_ref]
,A.[veh_reg_no]
,A.[policy_premium]
,A.[policy_commission]
,A.[policy_ipt]
,B.IsError
FROM dbo.[ActurisDW_Policy_Main] A
Left Join ( select distinct policy_ID, isError
from (
select policy_id, Case when status = 4 THEN 'Y' ELSE 'N' end as isError
from IntDB_Policy ) d
where isError = 'Y' ) b
ON A.policy_band_ref = B.policy_id ) AS X

INNER JOIN (
select max(version_reference) AS MAXVERSION, [dbo].[fnPolicyNumberLogic](insurer_policy_no,effective_date_key,DENSE_RANK() OVER (PARTITION BY [insurer_policy_no] ORDER BY [policy_band_ref])) AS LOOKUPPOLICY
from dbo.[ActurisDW_Policy_Main]
group by insurer_policy_no, effective_date_key, [policy_band_ref]
) TEMP
ON TEMP.LOOKUPPOLICY = X.FinalPolicyRefernce AND TEMP.maxversion = X.version_reference

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources