Non-Distinct Query 2

[adamr99]

I have the following records with 4 fields:

test1, 1, 1
test1, 3, 4
test3, 1, 1
test4, 1, 2
test4, 1, 3

I would like to return:

test1, 1, 1
test4, 1, 2
test4, 1, 3
test1, 3, 4


That is, I would like to get the non-distinct records ONLY non-distinct on only the first field. Note that on the first 2 records, the third field is different.

Thanks!
Adam

 

[SkipVought]

Hi,

How 'bout this...
[tt]
SELECT first(t1.Fld1), t1.Fld2, t1.Fld3
FROM MyTable t1, MyTable t2
WHERE (t1.Fld2=t2.Fld2) AND (t1.Fld3=t2.Fld3)
GROUP BY t1.Fld2, t1.Fld3
[/tt]


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[adamr99]

Here is the exact code that I ended up with:
SELECT First(t1.test1) AS FirstOftest1, t1.test2, t1.test3
FROM Table1 AS t1, Table1 AS t2
WHERE (((t1.test2)=(t2.test2)) AND ((t1.test3)=(t2.test3)))
GROUP BY t1.test2, t1.test3;



Here is the result of that code:
test3 1 1
test4 1 2
test4 1 3
test1 3 4


Here is what I wanted: (sorted correctly):
test1, 1, 1
test1, 3, 4
test4, 1, 2
test4, 1, 3

It left out the other test1 and put in the test3 when it shouldn't have been in there.

Thanks!
Adam

 

[SkipVought]

Then you wanted Last instead of First.



Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[adamr99]

SkipVought-

YOU ROCK. Ok, now, my fake data was a little misleading. Is there anyway that I can include records that are EXACTLY the same as well?

test5, 1, 1
test5, 1, 1

??

Thanks!
Adam

 

[adamr99]

Sorry for the multiple posts. Also, I would like to include records that are the same in only one of the two last columns. That is:

test6, 1, 1
test6, 1, 000

The only thing that I care about is the first column being the same. The rest is a "don't care" condition".

Thanks!
Adam

 

[SkipVought]

Do you mean that if they are EXACTLY identical that you want them REPEATED in the resultset?

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[adamr99]

Yes. Basically, if the first field is duplicated in any other first field (regardless of what the other fields are) I want it in the resultset.


test7, 1, 2
test7, 1, 6 YES

test7, 1, 1
test7, 1, 1 YES

test7, 7, 1
test7, 0, 1 YES

testX, 1, 1
test4, 1, 1 NO

Sorry I am so confusing.

 

[adamr99]

Any Ideas?

 

[adamr99]

Hey, for those of you searching the archives on this. I ended up finding a solution.

http://www.tech-archive.net/Archive/SQL-Server/microsoft.public.sqlserver.server/2004-04/3108.html

select
l.*
from letters as l
join
( select letter
from letters
group by letter
having count (*) > 1
) as x on x.letter = l.letter

 

[as0125]

Hi Adam,

This issue is exactly what I'm currently working on also -- but I'm new to Access/SQL -- so would it be okay if you could clarify the solution for me?

I don't understand what "x" is in reference to -- a second table?

What I'm trying to do is find multiple records that were entered on the same date. I'd like the query to produce the following:
1 - the entry date
2 - the name of company duplicated
3 - fields that differ between the two records.

Thanks for any help!

 

[lespaul]

x is an alias to the recordset created by the statement:

select letter
from letters
group by letter
having count (*) > 1

Leslie

 

[as0125]

Leslie, thanks for the clarification.

Now I have another question (for anyone). The query filters out all non-duplicated records. The following info is the expected return from the query:

ID Date Company Description
1 06/23/04 ABC test1
2 06/23/04 ABC test2
3 06/23/04 XYZ test1
4 06/23/04 XYZ test2

However, each ID is displayed twice. So in total, I have 8 records, rather than 4. Here is my query:

SELECT p1.*
FROM Table AS p1
INNER JOIN
(SELECT Date, Company
FROM Table
GROUP BY Date, Company
having count (*) > 1 )
AS p2 ON p2.Date = p1.Date

What modification/addition do I need in my query in order to prevent displaying the same ID multiple times?

 

[lespaul]

add the distinct keyword to your select statement and the duplicates won't show up:

SELECT DISTINCT p1.*
FROM Table AS p1
INNER JOIN
(SELECT Date, Company
FROM Table
GROUP BY Date, Company
having count (*) > 1 )
AS p2 ON p2.Date = p1.Date


Leslie

 

[as0125]

Leslie,

Thanks very much!