SQL SERVER COMPARE SAME COLUMN

Question

Hello all,

I face a little problem in my sql query.I want o compare data in same table and make a simple output table.

I have a table A here have column 1,column 2,column 3,column 4,column 5.

column 5 is flag column its flag data "U" and "D". i need to find those all value which have "U" and "D" flag with all other column data same .Mean all other column value same but comuln5 value different.

Sorry for my english .

if need more details please ask me.

Thanks for help

Muzahid

Answer 1

Not sure, I understood your question...

But Try the below

Create table tablename (col1 int ,Col2 int, Col3 int,Col4 int,Col5 char(1))

Insert into tablename values(1,2,3,4,'U'),(1,2,3,4,'D'),(2,3,4,5,'U'),(2,3,4,5,'I'),(3,4,5,6,'U')

;With cte
as(
Select *,COUNT(Case when Col5 in('U','D') then 1 else NULL end)Over(partition by Col1,Col2,Col3,Col4 )  Cnt 
From tablename)
Select Col1,Col2,Col3,Col4,Col5 From cte where Cnt>1

Drop table tablename

Answer 2

Please post sample data + desired result. Always state what version you are using...

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

Answer 3

Hi Muzahid,

I hope this is what you meant:

SELECT
	COL1 ,
	COL2 ,
	COL3 ,
	COL4
FROM
	TableName
WHERE
	COL5 IN ('U' , 'D')
GROUP BY
	COL1 ,
	COL2 ,
	COL3 ,
	COL4
HAVING
	COUNT (DISTINCT COL5) = 2;

Good luck!

--------------------------------------------
Guy Glantser
SQL Server Consultant & Instructor
Madeira - SQL Server Services
http://www.madeira.co.il

Answer 4

Not sure, I understood your question...

But Try the below

Create table tablename (col1 int ,Col2 int, Col3 int,Col4 int,Col5 char(1))

Insert into tablename values(1,2,3,4,'U'),(1,2,3,4,'D'),(2,3,4,5,'U'),(2,3,4,5,'I'),(3,4,5,6,'U')

;With cte
as(
Select *,COUNT(Case when Col5 in('U','D') then 1 else NULL end)Over(partition by Col1,Col2,Col3,Col4 )  Cnt 
From tablename)
Select Col1,Col2,Col3,Col4,Col5 From cte where Cnt>1

Drop table tablename

Thanks Latheesh NK  Its work on my table. thanks u so much

Answer 5

Thanks all Latheesh NK  help work on my table .

Thanks again for quick help .

Muzahid

Answer 6

Without CTE also you could achieve this. Just FYI.

SELECT T1.*
FROM tablename T1 JOIN tablename T2 ON T1.col1=T2.col1 AND T1.col2=T2.col2 AND T1.col3=T2.col3 AND T1.col4=T2.col4 AND T1.col5<>T2.col5 AND T1.Col5 IN ('U','D')
AND T2.Col5 IN ('U','D')

---

Chaos isn’t a pit. Chaos is a ladder. Many who try to climb it fail and never get to try again. The fall breaks them. And some are given a chance to climb, but they refuse. They cling to the realm, or the gods, or love. Illusions. Only the ladder is real. The climb is all there is.