question

iermis avatar image
iermis asked

Select distinct rows based on a column that have all possible values,Select distinct records that have all possible values of a column

Hi, I m trying to select distinct these rows that have all possible values of a given column. Please check the example bellow. - ID_REC ID_COMMAND ID_STORE - 1 100 1 - 2 110 1 - 3 120 1 - 4 100 2 - 5 110 2 Given that ID_STORE column has values 1 and 2, I want to select distinct these rows that have ID_STORE 1 and 2. In this example I want output like - ID_COMMAND - 100 - 110 Any idea? (sorry for my bad english)
selectdistinct
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

HINT: you need to use GROUP BY, COUNT, and HAVING in the query :)
0 Likes 0 ·
Dear DenisT can you explain this a little bit more?
0 Likes 0 ·
\+1 for giving example data and expected output
0 Likes 0 ·
Thank you all for your replies. I ve solved the issue using your advices.
0 Likes 0 ·
iainrobertson avatar image
iainrobertson answered
Assuming that you don't know how many possible values are present in ID_STORE, you'll need to either pre-calculate the expected count: create table #SampleData (ID_REC int, ID_COMMAND int, ID_STORE int) insert #SampleData select 1, 100, 1 union all select 2, 120, 1 union all select 3, 130, 1 union all select 4, 140, 1 union all select 5, 100, 2 union all select 6, 120, 2 union all select 7, 140, 2 union all select 8, 100, 2 union all select 9, 110, 3 union all select 10, 120, 3 union all select 11, 130, 3 union all select 12, 140, 3 -- using pre-count - only works if combination of ID_COMMAND and ID_STORE is unique declare @ID_STORE_Count int select @ID_STORE_Count = count(distinct ID_STORE) from #SampleData select ID_COMMAND from #SampleData group by ID_COMMAND having count(*) = @ID_STORE_Count go -- using pre-distinct and pre-count declare @ID_STORE_Count int select @ID_STORE_Count = count(distinct ID_STORE) from #SampleData ; with cte as ( select distinct ID_COMMAND , ID_STORE from #SampleData ) select ID_COMMAND from cte group by ID_COMMAND having count(*) = @ID_STORE_Count Or use a method that doesn't rely on a count: -- gather the set of unique ID_STORE values ; with cte as ( select distinct ID_STORE from #SampleData ) , cte2 as ( -- cross join the ID_REC to produce a list of all possible combinations select distinct s.ID_COMMAND , cte.ID_STORE from cte cross join #SampleData s ) , cte3 as ( -- left join back to the data to find missing combinations select c_ID_COMMAND = c.ID_COMMAND , s_ID_STORE = s.ID_STORE from cte2 c left join #SampleData s on c.ID_COMMAND = s.ID_COMMAND and c.ID_STORE = s.ID_STORE ) select distinct c_ID_COMMAND from cte3 c where not exists ( select 'null combination' from cte3 where c_ID_COMMAND = c.c_ID_COMMAND and s_ID_STORE is null ) Which is admittedly a bit contrived and I suspect likely to be less efficient. It depends on data volume and how your table is indexed. You would have to test in your environment to be sure.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

DenisT avatar image
DenisT answered
You need to group by ID_COMMAND and count them, then filter out > 1 using the HAVING clause.
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thank you, I will try it.
1 Like 1 ·
Depending on your exact dataset (e.g. whether it is possible to have two ID_COMMAND with the same ID_STORE value), you may want to use COUNT (DISTINCT ID_STORE) to filter your values.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.