July 1, 2012 at 3:28 am
Hi SQL Gurus,
I have a table below here which display same records by different subgroups.
ID | ACCOUNT | ORGANIZATION | SUBGROUP | AMOUNT
-----------------------------------------------------
1 | 1001 | aaa | AL | 100
2 | 1001 | aaa | AL | 50
3 | 1001 | aaa | AG | 100
4 | 1001 | aaa | AG | 50
5 | 1003 | vvv | ST | 99
6 | 1003 | vvv | TD | 99
7 | 1002 | ggg | GO | 58
How can I filter out the different subgroups in order to display only one subgroup if different subgroups appearing in the same records? This is the expected result:
ID | ACCOUNT | ORGANIZATION | SUBGROUP | AMOUNT
-----------------------------------------------------
1 | 1001 | aaa | AL | 100
2 | 1001 | aaa | AL | 50
5 | 1003 | vvv | ST | 99
7 | 1002 | ggg | GO | 58
Below is the DDL and sample data for this:
DECLARE @SAMPLE TABLE
(
ID INT NOT NULL,
ACCOUNT CHAR(4) NOT NULL,
ORGANIZATION CHAR(3) NOT NULL,
SUBGROUP CHAR(2) NOT NULL,
AMOUNT int NOT NULL
)
INSERT @SAMPLE
VALUES (1, '1001', 'aaa', 'AL', 100),
(2, '1001', 'aaa', 'AL', 50),
(3, '1001', 'aaa', 'AG', 100),
(4, '1001', 'aaa', 'AG', 50),
(5, '1003', 'vvv', 'ST', 99),
(6, '1003', 'vvv', 'TD', 99),
(7, '1002', 'ggg', 'GO', 58)
Please advise. Thanks.
July 1, 2012 at 3:50 am
What is the criteria for eliminating subgroups from the result set? Hard to give you an answer when we don't know the rules that need to be applied.
July 1, 2012 at 3:50 am
Which subgroup you want to keep. Based on your output I have decided that you want to keep the subgroup which has minimum ID value.Below sql will give you the output.
select ID,Account,Organization,SUBGROUP,Amount from
(
select *,ROW_NUMBER() over(partition by Account,Organization,Amount order by id asc) as rn
from @SAMPLE
) dta where rn =1
order by ID asc
You can use CTE as well
;with mycte
as
(
select *,ROW_NUMBER() over(partition by Account,Organization,Amount order by id asc) as rn
from @SAMPLE
)
select ID,Account,Organization,SUBGROUP,Amount
from mycte
where rn =1
order by ID asc
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
July 1, 2012 at 8:36 am
Lynn Pettis (7/1/2012)
What is the criteria for eliminating subgroups from the result set? Hard to give you an answer when we don't know the rules that need to be applied.
Hi Lynn,
I would like to eliminate one of the subgroups which has the same set of account, organization and amount with other subgroup. If the row has it's own set of records in it's subgroup, I will remain that row.
Regards.
July 1, 2012 at 10:34 am
is this any help?
SELECT s.ACCOUNT,
s.ORGANIZATION,
MAX(s.SUBGROUP),
s.AMOUNT
FROM @SAMPLE s
INNER JOIN (SELECT DISTINCT
ACCOUNT,
ORGANIZATION,
AMOUNT
FROM @SAMPLE) v ON s.ACCOUNT = v.ACCOUNT
AND s.ORGANIZATION = v.ORGANIZATION
AND s.AMOUNT = v.AMOUNT
GROUP BY s.ACCOUNT,
s.ORGANIZATION,
s.AMOUNT
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 1, 2012 at 11:29 am
yingchai (7/1/2012)
Lynn Pettis (7/1/2012)
What is the criteria for eliminating subgroups from the result set? Hard to give you an answer when we don't know the rules that need to be applied.Hi Lynn,
I would like to eliminate one of the subgroups which has the same set of account, organization and amount with other subgroup. If the row has it's own set of records in it's subgroup, I will remain that row.
Regards.
Could you post some additional sample data and expected results that helps show this? I think I understand but seeing it would help a lot.
July 1, 2012 at 2:18 pm
How do you decide which group to keep if there is an exact match between the sets on the duplicate criteria? Also, do you keep the group that is a superset if one set is completely contained by another that has additional rows of data?
July 1, 2012 at 2:22 pm
Using the following sample data, what would your expected results be?
DECLARE @SAMPLE TABLE
(
ID INT NOT NULL,
ACCOUNT CHAR(4) NOT NULL,
ORGANIZATION CHAR(3) NOT NULL,
SUBGROUP CHAR(2) NOT NULL,
AMOUNT int NOT NULL
)
INSERT @SAMPLE
VALUES (1, '1001', 'aaa', 'AL', 100),
(2, '1001', 'aaa', 'AL', 50),
(3, '1001', 'aaa', 'AG', 100),
(4, '1001', 'aaa', 'AG', 50),
(5, '1003', 'vvv', 'ST', 99),
(6, '1003', 'vvv', 'TD', 99),
(7, '1002', 'ggg', 'GO', 58),
(8, '1001', 'aaa', 'AG', 100),
(9, '1001', 'aaa', 'AG', 80);
July 1, 2012 at 10:39 pm
pls try below code.
select * into #temp from @SAMPLE
order by SUBGROUP
select * from
(select *,row_number() over( partition by amount order by amount asc) rank
from #temp)k
where rank=1
July 1, 2012 at 11:22 pm
Lynn Pettis (7/1/2012)
Using the following sample data, what would your expected results be?
DECLARE @SAMPLE TABLE
(
ID INT NOT NULL,
ACCOUNT CHAR(4) NOT NULL,
ORGANIZATION CHAR(3) NOT NULL,
SUBGROUP CHAR(2) NOT NULL,
AMOUNT int NOT NULL
)
INSERT @SAMPLE
VALUES (1, '1001', 'aaa', 'AL', 100),
(2, '1001', 'aaa', 'AL', 50),
(3, '1001', 'aaa', 'AG', 100),
(4, '1001', 'aaa', 'AG', 50),
(5, '1003', 'vvv', 'ST', 99),
(6, '1003', 'vvv', 'TD', 99),
(7, '1002', 'ggg', 'GO', 58),
(8, '1001', 'aaa', 'AG', 100),
(9, '1001', 'aaa', 'AG', 80);
Hi Lynn,
This will be the expected result:
INSERT @SAMPLE
VALUES (1, '1001', 'aaa', 'AL', 100),
(2, '1001', 'aaa', 'AL', 50),
(5, '1003', 'vvv', 'ST', 99),
(7, '1002', 'ggg', 'GO', 58),
(9, '1001', 'aaa', 'AG', 80);
Thanks.
July 2, 2012 at 12:33 am
Ddi you try this one
;with mycte
as
(
select *,ROW_NUMBER() over(partition by Account,Organization,Amount order by id asc) as rn
from @SAMPLE
)
select ID,Account,Organization,SUBGROUP,Amount
from mycte
where rn =1
order by ID asc
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply