SQLTeam.com | Weblogs | Forums

Query for two columns


#1

I have three columns I need to find wrong records ...
Rows with value x in third column should have all values C in second column.
How I can pull all those wrong together with right one C..as you see some of them are with A or B ..

1 A x
2 A x
3 B x
4 B x
5 C x
6 D y
7 E z


#2

here you go

drop table #temp

Select *
into #temp
from (
Select 1 as Column1, 'A' as Column2,'x' as Column3
Union all
Select 2 as Column1, 'A' as Column2,'x' as Column3
Union all
Select 3 as Column1, 'B' as Column2,'x'as Column3
Union all
Select 4 as Column1, 'B' as Column2,'x' asColumn3
Union all
Select 5 as Column1, 'C' as Column2,'x'as Column3
Union all
Select 6 as Column1, 'D' as Column2,'y' as Column3
Union all
Select 7 as Column1, 'E' as Column2,'z' as Column3
) k

select * from #temp
where Column2 <> 'C' and Column3 = 'x'

update #temp
set Column2 = 'C'
where Column2 <> 'C' and Column3 = 'x'

select * from #temp


#3

I gave example of values in column .. but I do not know those values.
The values are not same in whole table ...
Also there are many records as x ..
Basically, column3 is account number but in column 2 i have some wrong data of person IDs.
For some reason person ID is created multiple times for same person, and same account is attached to it.
I have to find those rows.


#4

finding multiple x with out knowing the value

drop table #temp

Select *
into #temp
from (
Select 1 as Column1, 'A' as Column2,'x' as Column3
Union all
Select 2 as Column1, 'A' as Column2,'x' as Column3
Union all
Select 3 as Column1, 'B' as Column2,'x'as Column3
Union all
Select 4 as Column1, 'B' as Column2,'x' asColumn3
Union all
Select 5 as Column1, 'C' as Column2,'x'as Column3
Union all
Select 6 as Column1, 'D' as Column2,'y' as Column3
Union all
Select 7 as Column1, 'E' as Column2,'z' as Column3
) k

select *,ROW_NUMBER() over (partition by Column3 order by Column2)rn from #temp


#5

Check these three columns ...Here is some data from my table.
I need only records like records where I have value 222 in third column.
As you see second column is messed. it should be same person ID as ll others have ...

6128 100092 1446
6129 100092 1446
6131 100092 1446
6133 100092 1446
6134 100092 1446
5108 99541 222
5110 915 222
5114 915 222
5077 1128 222
5346 99919 2345637
5348 99919 2345637


#6

For your example of 222 in column 3 there are four rows. Two with 915 in column 2, and one each with 99541 and 1128. Howe do you determine which of the three - 915, 99541, or 1128 is the correct value that should have been in column 2?


#7

We will update manually that ...
I just need to find records like that ...
I need query which will pull records like those ..
5108 99541 222
5110 915 222
5114 915 222
5077 1128 222


#8
SELECT
	Col3
FROM
	YourTable
GROUP BY
	Col3
HAVING
	MAX(Col2) <> MIN(Col2);

#9

Coll3 is not Integer ..
Col1 and Col2 are integers.

Error;
Conversion failed when converting the nvarchar value '22-33-44' to data type int.


#10

What is the query you are using? The query I posted should work whether col3 is an integer or not, or whether col2 is integer or not.


#11

I show you error;
That data 22-33-44" is coming from Col3.

Conversion failed when converting the nvarchar value '22-33-44' to data type int.


#12

You could try

SELECT
	Col3
FROM
	YourTable
GROUP BY
	Col3
HAVING
	MAX(CAST(Col2  AS VARCHAR(32))) <> MIN(CAST(Col2  AS VARCHAR(32)));

#13

Thank you JamesK for your replay.
Your query return only one row of Col3 for that particular value 222. I would need all 4 rows of that one, which is bad in my example. Also Col1 is important. That is unique key and it should help me to show all rows.
I made query, but query return also those they are good.
I need rows only where Col2 is messed ...

select Col1,Col2,Col3 from MyTable
where Col3 in
(select Col3 from MyTable group by Col3
having Count(Col3) > 1)


#14

You can use the output of the query to pick all the rows that have the same col3

SELECT y1.* 
FROM 
	YourTableNameHere AS y
	INNER JOIN
	(
		SELECT
			Col3
		FROM
			YourTable
		GROUP BY
			Col3
		HAVING
			MAX(CAST(Col2  AS VARCHAR(32))) <> MIN(CAST(Col2  AS VARCHAR(32)))
	) y2 ON y2.Col3 = y1.Col3;

#15

Thank you jamesK for replay.
Unfortunately, this one return many good too ...
Like this one .. as you see Col2 is good...all same.

6044 100070 07182014
6048 100070 07182014
6049 100070 07182014
6057 100070 07182014
6059 100070 07182014
6061 100070 07182014


#16

There must be something else beyond what you posted. In the query below, I am using the data you posted in your latest post and using it in the query. When you run the query, it returns nothing, which is what one would expect. You can copy this code to an SSMS window and run it.

CREATE TABLE #tmp(col1 INT, col2 INT, col3 NVARCHAR(32));
INSERT INTO #tmp VALUES
(6044,100070,'07182014'),
(6048,100070,'07182014'),
(6049,100070,'07182014'),
(6057,100070,'07182014'),
(6059,100070,'07182014'),
(6061,100070,'07182014')

SELECT y1.* 
FROM 
	#tmp AS y1
	INNER JOIN
	(
		SELECT
			Col3
		FROM
			#tmp
		GROUP BY
			Col3
		HAVING
			MAX(CAST(Col2  AS NVARCHAR(32))) <> MIN(CAST(Col2  AS NVARCHAR(32)))
	) y2 ON y2.Col3 = y1.Col3;
	
DROP TABLE #tmp;

#17

Thank you very much JamesK.
Yes, you are right, i had mistype ...It works.
Thanks a lot.