asked on

Sql Server query to compare rows

Hi ,
I have a table for Documents with following fields
DocumentNo
DocRevision
Location
.............. There are more fields

Following is the sample data .
Doc1, Rev1, Loc1
Doc1,Rev1, Loc1
Doc1,Rev1,Loc2
Doc2,Rev1,Loc2
Doc2,Rev1,Loc2

For Given DocumentNo and DocRevision , Location has to be same . In this case Doc1,Rev1 has wrong data .

I am looking for a Sql server query that can bring the wrong data ( In the sample, the query should bring all the three rows of Doc1,Rev1)

Ryan Chong

try something like this:

declare @Documents table
(
	DocumentNo varchar(10),
	DocRevision varchar(10),
	Location varchar(30)
)
insert into @Documents
values
('Doc1', 'Rev1', 'Loc1'),
('Doc1', 'Rev1', 'Loc1'),
('Doc1', 'Rev1', 'Loc2'),
('Doc2', 'Rev1', 'Loc2'),
('Doc2', 'Rev1', 'Loc2');

with cte as
(
	select DocumentNo, DocRevision, Location
	from @Documents
	group by DocumentNo, DocRevision, Location
), cte2 as
(
	select DocumentNo, DocRevision
	from cte
	group by DocumentNo, DocRevision
	having count(*) > 1
)
select a.*
from @Documents a inner join cte2 b
on a.DocumentNo = b.DocumentNo and a.DocRevision = b.DocRevision

Open in new window

Sam OZ

ASKER

Thanks Chong . My apologies. There is some difference in the DB structure
Documents table
DocumentNo
DocRevision
Location
ItemNo

Items Table
ItemName
ItemNo

ItemNo joins the two tables

For a given ItemName, DocRevision the Location should be same

I need to get entries where there are same ItemName ,Docrevision combination but with different location

ASKER CERTIFIED SOLUTION

Ryan Chong

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial