"Max" over multiple tables - 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

General SQL Server Forums

New to SQL Server Programming

"Max" over multiple tables

Author

Topic

rbisset
Starting Member

2 Posts

Posted - 2009-10-14 : 15:35:45

Hi. I'm really new to SQL programming and have been struggling with this query all day! In theory it seemed quite easy but I can't seem to get it to work properly.

I need to create a query that finds the last diary entry that has been created against a customers policy. When a new diary entry is created it is designated with a suffix number which increments by one every time. I also need to cross reference the customer to see which source code was used.

So I need to look in 2 tables for the information I require.

diary prospect
------- ----------
ref@ ref@
suffix@ source#
Doc_type#

And the report needs to show:

Polref@ (from diary), suffix@ and doc_type# (letter type)

I managed to get the polref and suffix to display the highest record for each reference using max and distinct but when I try to include the doc_type column it just ignores "max" and shows all letters.

ie

Polref@ suffix@
ABIN002PA1 1
ABIN002TL1 1
ABLE002TL1 2

Which shows ABLE002TL1 has more than 1 letter

Polref@ suffix@ Doc_type
ABIN002PA1 1 Intro
ABIN002TL1 1 Intro
ABLE002TL1 1 Intro
ABLE002TL1 2 Renewal

Any help will be great! Thanks.

Code from Query but have SQL 2005 as well.

SELECT Dadiary."Polref@", Max(Dadiary."Suffix@") AS 'Max of Suffix@'
FROM Opengi.dbo.Dadiary Dadiary, Opengi.dbo.Daprospect Daprospect
WHERE Dadiary."B@" = Daprospect."B@" AND Dadiary."Ref@" = Daprospect."Ref@" AND ((Daprospect."Source#" In ('Spa MULTI','Spa TRANSFERRED')))
GROUP BY Dadiary."Polref@"
ORDER BY Dadiary."Polref@"

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-10-14 : 16:15:38

I'm not 100% how the other fields come into play ("B@" and "Ref@") but, maybe this will help?

SELECT 
	T."Polref@",
	T."Suffix@" AS 'Max of Suffix@'
	Daprospect.Doc_type
FROM
	Opengi.dbo.Daprospect AS Daprospect
INNER JOIN
	(
		SELECT 
			"Polref@", 
			"Suffix@",
			"B@",
			"Ref@",
			ROW_NUMBER() OVER (PARTITION BY "Polref@" ORDER BY "Suffix@" DESC) AS RowNum
		FROM 
			Opengi.dbo.Dadiary
	) AS T	
	ON T."B@" = Daprospect."B@" 
	AND T."Ref@" = Daprospect."Ref@" 
WHERE 
	RowNum = 1
	AND ((Daprospect."Source#" In ('Spa MULTI','Spa TRANSFERRED')))
ORDER BY 
	T."Polref@"

PS: This could probably also be done by doing the GROUP BY as a query in a derived table and using that to join to or using CROSS APPLY.

EDIT: Forgot ORDER BY.

rbisset
Starting Member

2 Posts

Posted - 2009-10-14 : 17:27:08

Thanks for the quick reply.

I forgot to add that B@ relates to a branch id which is shared between the 2 tables. It doesn't really need to be linked as all the policies should be on 0. The Ref@ is a client ID which is the same in both tables. I assumed I needed that to form some relationship between the 2.

On another note it seems we have SQL 2000, not 2005 as I originally believed. It doesn't seem to support ROW_NUMBER.

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources