Speed Up In Statement 2

[perrymans]

I am using the following pass-through (Access as a front-end) to find duplicate member numbers and then return all the records that have the duplicate member numbers:

SELECT * FROM Tbl_Members WHERE (Tbl_Members.MemberNr) In
(select MemberNr from Tbl_Members
group by MemberNr HAVING count(MemberNr)>1)
order by MemberNr desc;

It is ridiculously slow, which means I am not doing it right, probably.

Please let me know how I can speed this up.

Thanks. Sean.

 

[IanWaterman]

Try

SELECT * FROM Tbl_Members a
inner join
(select MemberNr from Tbl_Members
group by MemberNr HAVING count(MemberNr)>1) b
on a.MemberNr = b.MemberNr

Ian

 

[TonyGroves]

That seems unnecessarily complicated. How about:
[tt]
select * from Tbl_Members
group by MemberNr HAVING count(MemberNr)>1
order by MemberNr desc;
[/tt]
Or am I missing something?

 

[r937]

the problem with that, tony, is that it doesn't return all the rows that have the duplicate member numbers

here's a sample table:

CREATE TABLE Tbl_Members
( MemberNr INTEGER 
, Name VARCHAR(99)
);
INSERT INTO Tbl_Members VALUES
 ( 1001, 'Tom' )
,( 1002, 'Dick' )
,( 1001, 'TomAgain' )
,( 1003, 'Harry' )

here's the correct result produced by perryman's query:

MemberNr Name
1001     Tom
1001     TomAgain

here's the incorrect result produced by your query:

MemberNr Name
1001     Tom

r937.com | rudy.ca

 

[TonyGroves]

I was missing something.

 

[r937]

rule of thumb: any time you use GROUP BY along with the dreaded, evil "select star" you almost surely are not going to get what you think you're going to get

besides, you can't even do it in any other database system besides mysql, so it's something that should really be unlearned



r937.com | rudy.ca

 

[perrymans]

Thanks fellers, I will try once I get home.

Sean.