SQL server,find distinct groups in a table

Question

I have table

prdID    item     percentage
1        10         50
1        20         50
2        10         50
2        20         50
3        20         30
3        20         70
4        10         50
4        20         30
4        30         20
5        10         50
5        20         50

I want to fetch the distinct groups.Basically the result set should be

group   item     percentage
1        10        50
1        20        50
3        20        30
3        20        70
4        10        50
4        20        30
4        30        20

2 and 5 are excluded from the result as they contain exactly the same item/percentage values as 1

Distinct over what set? Why are groups 1, 3, and 4 represented in the result set but not 2 and 5? — cdhowie, Apr 29 '13 at 19:36
basically product 1,2,5 have the same composition.so they are duplicates.so i need just prd 1 — user2333435, Apr 30 '13 at 02:58
Why is product 1 returned, why not 2 or 5? Is there some kind of ordering you want imposed to decide which of the duplicate groups are returned? Also, we need to know which database engine you are using as any answer might make use of engine-specific SQL features. — cdhowie, Apr 30 '13 at 14:46

score 0 · Answer 1 · answered May 17 '13 at 20:45

I think this is query you want:

SELECT MIN(prdId) AS group, item, percentage
FROM <tablename>
GROUP BY item, percentage

This will produce the following result on your example data:

group   item     percentage
1        10        50
1        20        50
3        20        30
3        20        70
4        30        20

(I think you made a small mistake in your example output by including the two other 4's. Their (item, percentage) values are duplicates of a group 1 and a group 3).

score 0 · Answer 2 · answered Jul 25 '13 at 12:00

select * 
from table_name t
inner join (
select min(prdID) as prdID
from
(
SELECT 
  table_name.prdID as prdID,
  STUFF((
    SELECT ',' + cast(item as varchar)+'~'+cast(percentage as varchar)
    FROM table_name t2
    WHERE t2.prdID = table_name.prdID
    FOR XML PATH (''))
,1,2,'') AS Names
FROM table_name
GROUP BY table_name.prdID
)t
group by Names)R
on t.prdID=R.prdID

SQL FIDDLE

SQL server,find distinct groups in a table

2 Answers2