Duplicate Queries
in [Queries]
|
Prev: IIf statement
Next: Overflow
From: Sylvain Lafontaine on 16 Jan 2010 19:40 The easiest way to solve these kind of problem is always to use the EXISTS statement. Later, you can use more advanced queries by using things HAVING and ORDER BY to retrieve/filter your duplicates but these require a deeper experience of SQL. The problem with EXISTS is that it require a primary key in order to detect the duplicate but I don't think that the fact it is missing in your design is really important. Here an example that could fill the bill for you; based on T-SQL for SQL-Server for creating the sample data: Select * into #t from (select 1 as idt, 'a' c1, 'c' c2, 'e' c3, 'f' c4 union all select 2, 'a', 'c', 'e', 'f' union all select 3, 'a', 'c', 'e', 'g' union all select 4, 'a', 'd', 'e', 'f' union all select 5, 'a', 'd', 'e', 'f' ) as SQ Select c1, c2, c3, c4 from #t t1 Where Exists (select * from #t t2 where t1.idt<>t2.idt and t1.c1=t2.c1 and t1.c2=t2.c2 and t1.c3=t2.c3 and t1.c4 <> t2.c4) Group by c1, c2, c3, c4 drop table #t I have added a Group By in order to filter the duplicate between 1 & 2 as you had in your very first example. -- Sylvain Lafontaine, ing. MVP - Windows Live Platform Blog/web site: http://coding-paparazzi.sylvainlafontaine.com Independent consultant and remote programming for Access and SQL-Server (French) "Tom" <Tom(a)discussions.microsoft.com> wrote in message news:CAF2925D-61D3-46C7-BFBD-7D3A7316EE41(a)microsoft.com... > I want the result to be If Col 1, 2 and 3 are duplicated then only want to > see the ones that Col 4 are Not duplicated. > > Columns > 1 2 3 4 5 etc > a c b h----changed-- this may take out the confusion > a c e f > a c e g > a d e f > a d e f > > Results Wanted: The a c e match in two rows but only want when the 4th > col > does not match > > a c e f > a c e g > > > > > > > -- > Tom > > > "John W. Vinson" wrote: > >> On Fri, 15 Jan 2010 10:33:01 -0800, Tom <Tom(a)discussions.microsoft.com> >> wrote: >> >> >Ok---Here is a try: >> > >> >Columns >> >1 2 3 4 5 etc >> >a c e f >> >a c e f >> >a c e g >> >a d e f >> >a d e f >> > >> >Results Wanted >> > >> >a c e f >> >a c e g >> > >> >Does this help? >> > >> >Thanks >> >> Not unless you meant to say >> >> a d e f >> >> instead of >> >> a c e g >> >> since a c e g is NOT duplicated and a d e f is. In fact the situation for >> a d >> e f appears to be absolutely identical to that for a c e f. Why should >> one be >> selected and not the other? >> >> -- >> >> John W. Vinson [MVP] >> . >> |