Get records having different data in 2 rows

[Vipul Mehta]

I have 2 columns (Account Number & State) with follwing set of records in my table Acc No State 1001 WA 1002 NY 1002 NY 1002 NY 1003 CA 1003 CA 1001 CA 1002 NY I want to pull such Account Numbers that has multiple states associated with that account. Looking at the existing records, output should be as shown below 1001 1002 What query should I write to get this result set?

Regards, Vipul Mehta

[Vipul Mehta]

Got this resolved

Create Table T1 (Col1 int, Col2 varchar(50))
Insert into T1 Select 1001,'WA'
Insert into T1 Select 1001,'CA'
Insert into T1 Select 1002,'WA'
Insert into T1 Select 1001,'WA'
Select Col1 From
(
Select *,ROW_NUMBER()Over(partition by Col1 Order by Col1) RN
From (Select Col1,Col2 From T1
Group by Col1,Col2)T1
)A Where RN>1

Regards, Vipul Mehta

[JammoD87]

Hi, An alternative way to retrieve the data would be...

SELECT [Acc No] FROM table
GROUP BY State
HAVING COUNT(State) > 1

Thanks,

[Arun Vasu]

Create Table T1 (Col1 int, Col2 varchar(50)) Insert into T1 Select 1001,'WA' Insert into T1 Select 1002,'NY' Insert into T1 Select 1002,'NY' Insert into T1 Select 1002,'NY' Insert into T1 Select 1003,'CA' Insert into T1 Select 1003,'CA' Insert into T1 Select 1001,'CA' Insert into T1 Select 1002,'NY' select distinct(col1) from t1 group by col1,col2 having count(col1)<2 output will come one record. because state only one record have multi state

[Mycroft Holmes]

Jammo gave this exact same answer 2 hours earlier and it was a lot more understandable. Try using the formatting tools for placing code, your answer will be much more readable!

Never underestimate the power of human stupidity RAH

[Bernhard Hiller]

Why do you expect "1002" to be among the output? Only "NY" is associated with "1002", though 4 times. But you said "multiple states".

[Bernhard Hiller]

That causes error message 8120 in Microsoft SQL server (similar message in MS Access):

Column [Acc No] is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

[Bernhard Hiller]

Arun's answer is clearly different from Jammo's. And Jammo's SQL statement fails with an error message.

[JammoD87]

Yeah, slight typo there did it on the quick, will have to read it properly next time!

SELECT [Acc No] FROM table
GROUP BY [Acc No], State
HAVING COUNT(State) > 1

Sorry about that :doh:

[Simon_Whale]

Nice answer. +5 can I suggest that you try to format your code so that it is easier to read? i.e.

SELECT DISTINCT(col1)
FROM t1
GROUP BY col1,col2
HAVING COUNT(col1) < 2

Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch

[Mycroft Holmes]

My bad - I did not look close enough at the answer.

Never underestimate the power of human stupidity RAH

[Niju1]

select [Acc No],count(*) from table group by [Acc No] having count(*)>1

[Lost User]

SELECT DISTINCT A.Acc_No,count(*)
FROM [Test].[dbo].[Table_2] A
JOIN [Test].[dbo].[Table_2] B
on A.Emp_state = B.Emp_state
group by A.Acc_No having COUNT(*)>1