May 30, 2012 at 4:10 pm
I have a table pcid, part and the ruslts of the query look like this:
(first I have a variable number in this case it's 3, could be 4, could be 2)
1,a
2,a
1,b
3,c
1,d
2,d
3,d
1,e
2,f
From this results I am interested in the 3 d's because there are 3 similar records of distinct column 1.
How would you identify the d's in these results?
May 30, 2012 at 4:14 pm
Not a lot to go on, but I'll take a shot:
SELECT col1, col2
FROM dbo.tablename
GROUP BY col1, col2
HAVING COUNT(DISTINCT col1) = (SELECT COUNT(DISTINCT col1) FROM dbo.tablename)
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
May 30, 2012 at 11:07 pm
This might help:
--Creating Table
Create Table Ex1
(Col1 int,
Col2 char(1) )
--Inserting Sample Data
Insert Into Ex1
Select 1, 'a'
Union ALL
Select 2, 'a'
Union ALL
Select 1, 'b'
Union ALL
Select 3, 'c'
Union ALL
Select 1, 'd'
Union ALL
Select 2, 'd'
Union ALL
Select 3, 'd'
Union ALL
Select 1, 'e'
Union ALL
Select 2, 'f'
--If it is for simple Identification yo can add an Id column using Row_Number() Over Partition
Select ROW_NUMBER() Over (Order By (Select NULL) ) As Id, * From Ex1
--Otherwise you can group the data according to col1 and then use Row_Number() Over Partition
Select *, ROW_NUMBER() Over (Partition By Col1 Order By Col2) as rn From Ex1
If these queries don't work then you would have to elaborate on your requirement a little more.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply