Check if two rows have a different value in a specific column

Question

Check if two rows have a different value in a specific column

jpi

SSC Rookie

Points: 33
More actions
September 9, 2015 at 5:17 am

#399009

Hi guys!
I hope i am right in this sub! I have googled my question, but everything i can find is code to check if there are two rows with THE SAME value in a column.
So here is what i need.
I have huge export files in a DB and i need to check if there are any datasets that have the same value in the first column, but a different in another one, via a query of course.
Like this:
ID IS NULL
1 1
2 1
3 0
1 0
The expected ID i get as a result of my query should be 1 in this case. I hope you get what my problem is.
Thanks in advance!
.penny

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Ed Wagner SSC Guru Points: 287026 More actions · Answer 1

jpi (9/9/2015)
Hi guys!
I hope i am right in this sub! I have googled my question, but everything i can find is code to check if there are two rows with THE SAME value in a column.
So here is what i need.
I have huge export files in a DB and i need to check if there are any datasets that have the same value in the first column, but a different in another one, via a query of course.
Like this:
ID IS NULL
1 1
2 1
3 0
1 0
The expected ID i get as a result of my query should be 1 in this case. I hope you get what my problem is.
Thanks in advance!
.penny

I'm not sure if I understand what your question is, but I'll give it a shot.

Let's say you have your ID column and you want to see if the ID2 column is equal. The calculated value should be a 1 if the columns are equal and 0 if they are not equal. This should do the trick.

SELECT ID, ID2, CASE WHEN ID = ID2 THEN 1 ELSE 0 END AreTheyTheSame

FROM dbo.table_name

WHERE ...

I hope I understood your question and that this helps. If not, take a few minutes to check out the link in my signature on how to best ask questions.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

What would be your expected result set from these rows?

1 1

2 1

3 0

1 0

2 1

3 0

3 1

John

Lowell SSC Guru Points: 323574 More actions · Answer 3

here's my guess on the requirements,i thought of two ways to do it, but i was not sure if you could have the same Id multiple times, all with the[IS_NULL] column as 1's, for example.

here's every Id that appears more than once, regardless of the [IS_NULL] column.

;WITH MySampleData([ID],[IS_NULL])

AS

(

SELECT '1','1' UNION ALL

SELECT '2','1' UNION ALL

SELECT '3','0' UNION ALL

SELECT '1','0'

)

SELECT * FROM (

SELECT row_number() OVER(Partition By ID ORDER BY [IS_NULL]) As RW,*

FROM MySampleData

) MyAlias

WHERE MyAlias.RW >1

here's all id's that exist with [IS_NULL] = 1, that have a counterpart that is not the same:

--shows duplicates

;WITH MySampleData([ID],[IS_NULL])

AS

(

SELECT '1','1' UNION ALL

SELECT '2','1' UNION ALL

SELECT '3','0' UNION ALL

SELECT '1','0'

)

SELECT * FROM MySampleData T1

INNER JOIN MySampleData T2

ON T1.ID = T2.ID AND T1.[IS_NULL] = 1

WHERE T1.[IS_NULL] <> T2.[IS_NULL]

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!