Data Extract - Partitioning

Hello,

I have a table containing 3 columns Department Name, RiskScenario and Cost. I am trying to create a data extract that contains the top 3 Risk Scenarios (sorted by Cost) per Department.

I tried using this sql statement in MSQuery but it doesn't work. Any ideas where I'm going wrong or if there is a simpler way to do this?

Select * from (
Select DepartmentName, `Risk Scenario`, Cost, row_number() OVER (PARTITION BY DepartmentName order by Cost) rn
FROM 'Departmental Risks`) where rn <=3

Please help. Just can't figure this out!

Meera

Meera:

Exactly what kind of error(s) are you experiencing? It appears to me that you have spurious "quotes" in your query.

Dave

|||

Meera:

I mocked the data with this table:

create table [Departmental Risks]
( DepartmentName varchar(30),
[Risk Scenario] varchar(30),
cost numeric (9,2)
)
go
insert into [Departmental Risks] values ('Dept A', 'Scene 1', 45.32)
insert into [Departmental Risks] values ('Dept A', 'Scene 2', 29.95)
insert into [Departmental Risks] values ('Dept A', 'Scene 3', 71.45)
insert into [Departmental Risks] values ('Dept A', 'Scene 4', 54.34)
insert into [Departmental Risks] values ('Dept B', 'Scene A', 21.45)

I then ran tried this query and obtained the result that follows:

select DepartmentName,
[Risk Scenario],
cost
from ( select DepartmentName,
row_number () over
( partition by DepartmentName
order by cost desc, [Risk Scenario]
) as Seq,
[Risk Scenario],
cost
from [Departmental Risks]
) a
where seq <= 3
order by DepartmentName,
cost desc,
[Risk Scenario]

-- -- Output: --

-- DepartmentName Risk Scenario cost
-- -
-- Dept A Scene 3 71.45
-- Dept A Scene 4 54.34
-- Dept A Scene 1 45.32
-- Dept B Scene A 21.45

See if this is in the direction you are aiming.

Dave

|||

Hi Dave,

I tried that in both MSQuery and MsAccess. In MS query the message i get say could not add the table '('. In Access I get a syntax error in query expresion 'row_number()...

Am at a loss!

Here is whatI used:

select [RA07 - Departmental Risks].DepartmentName,
[RA07 - Departmental Risks].[Risk Scenario],
[RA07 - Departmental Risks].annualriskcost
from ( select [RA07 - Departmental Risks].DepartmentName,
row_number () over
( partition by [RA07 - Departmental Risks].DepartmentName
order by [RA07 - Departmental Risks].annualriskcost desc, [RA07 - Departmental Risks].[Risk Scenario]
) as Seq,
[RA07 - Departmental Risks].[Risk Scenario],
[RA07 - Departmental Risks].annualriskcost
from [RA07 - Departmental Risks]
) a
where seq <= 3
order by [RA07 - Departmental Risks].DepartmentName,
[RA07 - Departmental Risks].annualriskcost desc,
[RA07 - Departmental Risks].[Risk Scenario];

|||

Is your target database an Access database or a SQL Server 2000 database? The errors you are getting indicates that the target database is not SQL Server 2005.

Dave

|||

Dave,

Its an Access Database