June 30, 2011 at 5:09 am
I have a table which lists finance reporting units (RU), buisness units(BU) and there respective consolidation entity (ENT).
Every RU and BU combo is related to more than 1 ENT. For testing purposes I need to select 1 entity for each RU - BU combo to feed into a system.
I have provided some data to explain the scenario.
CREATE TABLE #entity_list
(
RU varchar(8)
BU varchar(4)
ENT varchar(10)
)
INSERT INTO #entity_list
SELECT 'RU303100', 'BU99', 'NVR_ENT2_D' UNION ALL
SELECT 'RU303100', 'BU99', 'NVR_ENT2_E' UNION ALL
SELECT 'RU303100', 'BU99', 'NVR_ENT4_E' UNION ALL
SELECT 'RU303100', 'BU20', 'NVR_ENT3_D' UNION ALL
SELECT 'RU303100', 'BU20', 'NVR_ENT4_D' UNION ALL
SELECT 'RU303200', 'BU99', 'NVR_ENT3_A' UNION ALL
SELECT 'RU303200', 'BU99', 'NVR_ENT5_A' UNION ALL
SELECT 'RU303300', 'BU21', 'NVR_ENT6_F' UNION ALL
SELECT 'RU303300', 'BU21', 'NVR_ENT8_F'
In this scenario my output would be
RU303100 BU99 NVR_ENT2_D
RU303100 BU20 NVR_ENT3_D
RU303200 BU99 NVR_ENT5_A
RU303300 BU21 NVR_ENT8_F
It doesn't matter which ENT is picked as long as only one of the many for each RU BU combo is selected.
Any help would be appreciated
June 30, 2011 at 6:05 am
select RU,bu
,(select top 1 ent from @entity_list e2 where e2.RU = e1.RU and e2.BU = e1.BU)
from @#entity_list e1
group by RU,BU
could this work?
June 30, 2011 at 6:39 am
Many thanks. That seems to work. much appreciated
June 30, 2011 at 6:51 am
eseosaoregie (6/30/2011)
I have a table which lists finance reporting units (RU), buisness units(BU) and there respective consolidation entity (ENT).Every RU and BU combo is related to more than 1 ENT. For testing purposes I need to select 1 entity for each RU - BU combo to feed into a system.
....
It doesn't matter which ENT is picked as long as only one of the many for each RU BU combo is selected.
Any help would be appreciated
SELECT RU, BU, ENT
FROM (
SELECT RU, BU, ENT, rn = ROW_NUMBER() OVER (PARTITION BY RU,BU ORDER BY NEWID())
FROM #entity_list
) d
WHERE rn = 1
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 30, 2011 at 11:56 pm
ChrisM@Work (6/30/2011)
...code...
Hi Chris,
You might be interested in a slight tweak to that code. Take a look at the query plans for these:
-- Special distinct sort
SELECT
x.RU,
x.BU,
x.ENT
FROM
(
SELECT
*,
rn = ROW_NUMBER() OVER (
PARTITION BY RU, BU
ORDER BY RU, BU)
FROM #entity_list AS el
) AS x
WHERE
x.rn = 1;
-- Any covering index that provides order on (RU, BU) will do
CREATE CLUSTERED INDEX cx ON #entity_list (RU, BU);
-- Same query
SELECT
x.RU,
x.BU,
x.ENT
FROM
(
SELECT
*,
rn = ROW_NUMBER() OVER (
PARTITION BY RU, BU
ORDER BY RU, BU)
FROM #entity_list AS el
) AS x
WHERE
x.rn = 1;
Notice the special distinct sort in the first version, and the internal-only ANY aggregate function in the second.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
July 1, 2011 at 12:02 am
CELKO (6/30/2011)
SELECT DISTINCT report_unit_name, business_unit_name,MAX(something_consolidation)
OVER (PARTITION BY report_unit_name, business_unit_name)
FROM Foobar;
That's semantically correct (though why MAX?) but much less efficient in practice than any of the alternatives presented prior. The arguably more natural construction:
SELECT
el.RU,
el.BU,
MAX(el.ENT)
FROM #entity_list AS el
GROUP BY
el.RU,
el.BU;
...does the same thing, more efficiently. Personally, I prefer the ANY aggregate solution, because it most closely reflects the original stated requirement.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
July 1, 2011 at 2:05 am
SQLkiwi (6/30/2011)
ChrisM@Work (6/30/2011)
...code...Hi Chris,
You might be interested in a slight tweak to that code. Take a look at the query plans for these:
...
Notice the special distinct sort in the first version, and the internal-only ANY aggregate function in the second.
Many thanks Paul, I'll study this when time permits.
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply