Eliminating duplicate records

[anirudhadeo]

HI All,

I got to have some query Which I think Is some what complicated can some one please help me out..??

I have got one table say A(id1 integer,id2 integer,val char);

Id1 and id2 columns are not unique.
what I want is to have one query that will give me

1.The rows for which the id1 and id2 combination is unique.
i.e. the rows for which id1 and id2 can be a composite key in the resultset.

2. This is bit complicated for me to explain .
I want to get all the rows with the above condition as in 1.
As Well as I want that if there is any condition
where values of id1 and id2 are 1 and 2 respectively for one row and for another they are 2 and 1 respectively then
such rows should not b selected........( the vice versa combination should also be unique)

Is it possible to have a query for 1. and 2.

please suggest.

Anirudha

 

[Jonax]

Apart from the id1 & id2 columns - do you have a unique column? and what's it's name? This is not a bug - it's an undocumented feature...
;-)

 

[anirudhadeo]

No I dont have any unique column id for that table...
Anirudha

 

[tlbroadbent]

I'm unclear about the criteria of Query 2. I'll propose a solution and you tell me if it works for you. Query 1 is quite simple.

Query 1:

Select ID1, ID2 from TableA
Group By ID1, ID2

Query 2:
Select ID1, ID2
From TableA
Where Not Exists
(Select * From TableA As b
Where b.Id1=TableA.Id2
And b.Id2=TableA.Id1)
Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.