Here is my task

I have SQL 2000 tabl

id identit
field1 varcha
field2 varcha

there are some duplicate reocrds regarding the fileds field1 and filed

I want to see which ones are with the same field1 and different field2, and also to list the

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Is this what you meant??

CREATE TABLE Foobar
(foo_id INTEGER NOT NULL PRIMARY KEY,
col_1 VARCHAR(25) NOT NULL,
col_2 VARCHAR(25) NOT NULL);

The query is:

SELECT col_1
FROM Foobar
GROUP BY col_1
HAVING MIN(col_2) < MAX(col2);

Note that:

SELECT col_1
FROM Foobar
GROUP BY col_1
HAVING COUNT(col_2) > 1;

will not work, since you allowed duplicate (col_1,col_2) pairs in the
schema.

--CELKO--


*** Sent via Devdex http://www.devdex.com ***

You can think something on these lines.

-- To get same set of records
Select * from myTable
Where field1 like field2

-- To get records with different values
Select * from myTable
Where field1 NOT like field2

--
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp


[quoted text, click to view]

hi Elizabeta,

if i understand your requirement correctly you can try any one of the
following query.

--sample data
create table t (field1 varchar(5), field2 varchar(5))
insert into t values('x', 'y')
insert into t values('x', 'x')
insert into t values('y', 'x')

--to get the rows from table for repeating value of "field1" you can run
query.
select field1
from t group by field1 having count(*) > 1

--to get the rows for which field1 and field2 values are same you can run
query.
select * from t
where field1=field2

--to get the rows for which field1 and field2 values are not same you can
run query.
select * from t
where field1<>field2

--
Vishal Parkar
vgparkar@yahoo.co.in | vgparkar@hotmail.com

Thank you all for answering

The point is that if I have

duplicates for filed

are the valuse in field2 also the same or not

Example

Record

1,x1,x
2,x1,x
3,y1,y
4,y1,y
5,z1,z
6,z1,z

The quer
select field1, count(*
from tabl
group by field
having count(*) >

results i

x1
y1
z1

but I don't know are the duplicate in terms of pairs (filed1, field2
or they have same field1 values bu different field2 values

The next action over the table depends very much of these records, actualy I should clean the full duplicates and reoslve not full duplicates

I hope was more clear now

(This meand the conditions between columns are not viable - filed1=field2 or field1<> filed2,, this is not what I am looking at