Selecting the Highest 1 value per Organsation

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

Selecting the Highest 1 value per Organsation

Author

Topic

IainMaclean
Starting Member

9 Posts

Posted - 2009-08-17 : 22:14:58

Hope you can help me.

This query

Select Sum(remn_am)as Total_Remuneration, Jurisdiction, orgn_id as Organisation

From dbo.iamestab Establishments

Left Outer join dbo.Address Addresses
On establishments.residential_address_id = Addresses.adr_id

Left Outer Join dbo.tblQRegion PostCodeRegions
On ltrim(rtrim(addresses.post_cd)) = ltrim(rtrim(PostCodeRegions.post_code))

where orgn_id in (6818982,4934392,2694595,12672918)

Group By Orgn_id, Jurisdiction

Gives me the following results
Total_Remuneration, Jurisdiction, Organisation
9265700.00 1_MLBN 2694595
88497712.00 1_MLBN 4934392
552855319.00 1_MLBN 6818982
113170405.00 1_MLBN 12672918
2025949.00 2_KNX 2694595
4151006.00 2_KNX 4934392
131882373.00 2_KNX 6818982
71069211.00 2_KNX 12672918
1694823.00 3_GLNG 2694595
5816064.00 3_GLNG 4934392
99082310.00 3_GLNG 6818982
57078878.00 3_GLNG 12672918
1075561.00 4_BGO 2694595
674358.00 4_BGO 4934392
51079257.00 4_BGO 6818982
18182754.00 4_BGO 12672918
2090586.00 5_SPN 2694595
278132.00 5_SPN 4934392
14736525.00 5_SPN 6818982
9244206.00 5_SPN 12672918
1125198.00 6_ALBY 2694595
193039.00 6_ALBY 4934392
23351836.00 6_ALBY 6818982
11160545.00 6_ALBY 12672918
169007.00 7_MLDA 4934392
9182427.00 7_MLDA 6818982
5544298.00 7_MLDA 12672918

What i really want to be able to do is select only the Jurisdiction which has the highest Total_Remuneration. Eliminating duplicated organisations.

I suspect i need to do a subquery of some kind. Any advice would be appreciated.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-17 : 22:23:43

if you are using SQL 2005/2008, use row_number()

see http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/multipurpose-row-number-function.aspx

KH
[spoiler]Time is always against us[/spoiler]

IainMaclean
Starting Member

9 Posts

Posted - 2009-08-17 : 22:27:46

Sorry should have said, has to be accommodated by SQL 2000 for now.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-17 : 22:52:22

[code]
Select
Sum(remn_am)as Total_Remuneration,
~~Jurisdiction,~~
orgn_id as Organisation
From dbo.iamestab Establishments
Left Outer join dbo.Address Addresses
On establishments.residential_address_id = Addresses.adr_id
Left Outer Join dbo.tblQRegion PostCodeRegions
On ltrim(rtrim(addresses.post_cd)) = ltrim(rtrim(PostCodeRegions.post_code))
where orgn_id in (6818982,4934392,2694595,12672918)
Group By Orgn_id, ~~Jurisdiction~~
[/code]

KH
[spoiler]Time is always against us[/spoiler]

IainMaclean
Starting Member

9 Posts

Posted - 2009-08-17 : 22:56:40

Umm i need the jurisdiction with the most remuneration per organisation. Thanks for your input though.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-17 : 23:21:07

there will be more than one jurisdiction per organization so which one do you want ?

KH
[spoiler]Time is always against us[/spoiler]

IainMaclean
Starting Member

9 Posts

Posted - 2009-08-17 : 23:35:56

I want to select only the jurisdiction with the highest total remuneration value. For the following organisation i only want to select 1_MELB.

552855319.00 1_MLBN 6818982
131882373.00 2_KNX 6818982
99082310.00 3_GLNG 6818982
51079257.00 4_BGO 6818982
14736525.00 5_SPN 6818982
23351836.00 6_ALBY 6818982
9182427.00 7_MLDA 6818982

the query will not be specific to 1 organisation but many.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-18 : 02:27:17

only 1 row ? is it ?


Select TOP 1
       Sum(remn_am)as Total_Remuneration, 
       Jurisdiction, 
       orgn_id as Organisation
From   dbo.iamestab Establishments
       Left Outer join dbo.Address Addresses          On establishments.residential_address_id = Addresses.adr_id
       Left Outer Join dbo.tblQRegion PostCodeRegions On ltrim(rtrim(addresses.post_cd)) = ltrim(rtrim(PostCodeRegions.post_code))
where  orgn_id in (6818982,4934392,2694595,12672918)
Group By Orgn_id, Jurisdiction
Order By Total_Remuneration DESC

KH
[spoiler]Time is always against us[/spoiler]

ra.shinde
Posting Yak Master

103 Posts

Posted - 2009-08-18 : 02:29:21

Table structures will be required.

Rahul Shinde

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources