Complex Select? - 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

Complex Select?

Author

Topic

DirkNiblick
Starting Member

2 Posts

Posted - 2009-10-16 : 10:04:22

I'm sorry if I duplicate someone's post but I've searched quite a bit for answers to my goal but have come up empty. I'm new to the forums as well so bear with me:

I have three tables like this (sorry for the formatting):

Frames
ID Name
1 PS0
2 PS1
3 PS2
...

LPARS
ID Hostname FrameID
1 app01 1
2 app02 1
3 app11 2
4 app12 2
...

LPARConfigs
ID LPARID Memory CPU ConfigDate
1 1 8 2 3/1/2006
2 1 16 2 6/1/2006
3 1 16 4 9/1/2006
4 2 4 1 3/1/2006
5 2 4 2 9/1/2006
...

A Frame contains LPARS which each have different configurations in time. I would like to write a single query for SQL Server that would, for a given Frame.ID, return the sum of LPARConfigs.Memory and LPARConfigs.CPU for the latest LPARConfig record for each LPAR belonging to that Frame. For example, Frame #1 the answer would be 20 and 6 (summing the values from LPARConfigs #3 and #5). Any ideas? ...is it even possible?

Ifor
Aged Yak Warrior

700 Posts

Posted - 2009-10-16 : 10:59:57

[code]
-- *** Test Data ***
DECLARE @LPars TABLE
(
[ID] int NOT NULL
,Hostname varchar(20) NOT NULL
,FrameID int NOT NULL
)
INSERT INTO @LPars
SELECT 1, 'app01', 1
UNION ALL SELECT 2, 'app02', 1
UNION ALL SELECT 3, 'app11', 2
UNION ALL SELECT 4, 'app12', 2

DECLARE @LPARConfigs TABLE
(
[ID] int NOT NULL
,LPARID int NOT NULL
,Memory int NOT NULL
,CPU int NOT NULL
,ConfigDate datetime NOT NULL
)
INSERT INTO @LPARConfigs
SELECT 1, 1, 8, 2, '20060301'
UNION ALL SELECT 2, 1, 16, 2, '20060601'
UNION ALL SELECT 3, 1, 16, 4, '20060901'
UNION ALL SELECT 4, 2, 4, 1, '20060301'
UNION ALL SELECT 5, 2, 4, 2, '20060901'
-- *** End Test Data ***

;WITH ConfigOrder
AS
(
SELECT LPARID
,Memory
,CPU
,ROW_NUMBER() OVER (PARTITION BY LPARID ORDER BY ConfigDate DESC) AS RowNum
FROM @LPARConfigs
)
SELECT P.FrameID
,SUM(C.Memory) AS Memory
,SUM(C.CPU) AS CPU
FROM @LPars P
JOIN ConfigOrder C
ON P.[ID] = C.LPARID
AND C.RowNum = 1
GROUP BY P.FrameID
[/code]

DirkNiblick
Starting Member

2 Posts

Posted - 2009-10-16 : 12:29:48

Thank you Ifor for the great reply. I believe I understand what's going on there but what exactly does "ROW_NUMBER() OVER (PARTITION BY LPARID ORDER BY ConfigDate DESC) AS RowNum" do?

Ifor
Aged Yak Warrior

700 Posts

Posted - 2009-10-19 : 06:32:04

For each LPARID (PARTITION BY LPARID) it gives every row an unique number starting with 1 for the most recent ConfigDate (ConfigDate DESC).
(If you run SELECT * FROM ConfigOrder you will see the result.)

As you only want the most recent ConfigDate for each LPARID, the join then filters on RowNum = 1. (ie Any older rows are ignored.)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources