Re: Very chalanging question - Explanation

SELECT count(*),A.B,C,D
FROM tbl
GROUP BY A,B,C,D
HAVING count(*)>1

If that query takes 2 weeks to run then you've got serious and I mean
serious problems with your hardware, that type of query should take minutes
if not seconds most desktops let alone servers.

To aid performance for that specific query create your clustered index on A,
B, C and D.

--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials


"groupy" <liav.ezer@xxxxxxxxx> wrote in message
news:1149065676.453810.137020@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Ok, here is a asample table representing the problem more clearly

A | B | C | D
-----------------
a1 b1 c1 d1
a1 b2 c2 d2
a3 b3 c1 d3
a4 b4 c4 d3
a5 b5 c5 d5
a6 b6 c6 d3

Tha duplications are:
row 1+2 in param A
row 1+3 in param C
row 3+4+6 in param D
only row 5 is unique in all parameters.
conclusion: row 1+2+3+4+6 are the same user
goal: to find all duplicated rows & to delete them all accept one
instance to leave.

Note:
Finding that row 1similar to 2 in A & deleting it will loose data
because we won't know that row 1 is ALSO similar to 3 on C & later on
finding that 3 is similar to 4 & 6 on D & so on

The simple time consuming (about 2 weaks) query to acomplish the task
is:
SELECT count(*),A.B,C,D
FROM tbl
GROUP BY A,B,C,D
HAVING count(*)>1

I THANK YOU ALL



.

Relevant Pages

  • Re: A real challenge for someone out there? Please help!!!!!!!
    ... LineTeams will also be in the query ... and the Inbound table is one of the fieldlists you are linked to -- since each table has dates for everyday, it really doesn;t matter which table you use in the subquery as long as your Inbound, Outbound, and Times tables are also related on date using Links or criteria. ... Tbl Inbound Calls ... Answered Calls - Sum ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Very chalanging question - Explanation
    ... FROM tbl ... If that query takes 2 weeks to run then you've got serious and I mean ... Server Consultant ... row 1+3 in param C ...
    (comp.databases.ms-sqlserver)
  • RE: counting and sorting dates specific
    ... form calls the report and passes the date range for the query and for the ... FROM [tbl 1 CLIENT] ... When I run the report, the "dates" form accepts date range and then I am ...
    (microsoft.public.access.queries)
  • Form does not show previously entered records
    ... In analyzing this query last night, it seems that TBL - ... based on TBL - Aircraft, and set the Link Master Fields ...
    (microsoft.public.access.forms)
  • Data Type Mismatch In Criteria expression error when make-table query runs [1/1]
    ... I used a 'make table' because I had no time to work out why, when I tried to use the original 'select query' with a run-time criteria selection on 'next service date' I kept getting an unexpected 2nd prompt after inputting the date range. ... As I was under pressure to get some output I used the create table to get all the calculated data out and then queried that for the actual reports. ... [Tbl Vehicles].Registration_Number, ... In message, John Vinson writes ...
    (microsoft.public.access.queries)