Get records having different data in 2 rows

[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