Thanks all for your input. My original query wasn't far off from this
one. I know that 500,000 records isn't a huge amount of data (just
compared to what I am used to dealing with).
My problem turned out to be indexes. Waiting 30 minutes to try and run
the below query and getting impatient and quitting the query I looked up
indexes and how to use them (haven't really had that much to do with
indexes before) and voila - the below query takes < 1 minute.
Thanx all,
Steve
>>> [EMAIL PROTECTED] 20/05/2004 3:53:58 p.m. >>>
Steve,
You could probably do this by left joining the table on itself in a
query
and select for null in the join:
select mt1.myfield from mytable mt1
left join mytable mt2 on (mt1.myfield = mt2.myfield) and (mt2.weekno
= 2)
where mt1.weekno = 1 and mt2.myfield is null
should give you all the values in myfield which occur in mytable
with
weekno = 1
where there is NOT a corresponding value in myfield with weekno = 2
It's not obvious, coz you're using the left join for the opposite of
what
you would normally use it for.
You could also use select distinct....
HTH
Trevor
----- Original Message -----
From: "Steve Aish" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, May 20, 2004 2:30 PM
Subject: [DUG] Dealing with large amounts of data - comparing
> Hi y'all,
>
> I have a database that records approx 500,000 records per week in
one
> table.
>
> I want to compare week 1 and week 2 for example to see which of a
> certain field are in week 1 but not in week 2.
>
> I am using Interbase.
>
> I thought of running a query (until my pc ran out of hard drive
space
> with the temp file size).
>
> I thought the easiest (although probably not the best) way would be
to
> fill in a stringlist (I am only worried about 1 field) with week 1
and
> then fill another stringlist with week 2 and then compare them. I
just
> open a query with one field in it and iterate through the query and
>
> List.Add(Query1.FieldByName('Field').asString);
>
> This runs out of memory after 200,000 records or so. It is not the
> string list running out of memory - it is the Query1... ??? Confuses
> me.
>
> Any ideas?
>
>
> _______________________________________________
> Delphi mailing list
> [EMAIL PROTECTED]
> http://ns3.123.co.nz/mailman/listinfo/delphi
>
>
_______________________________________________
Delphi mailing list
[EMAIL PROTECTED]
http://ns3.123.co.nz/mailman/listinfo/delphi
_______________________________________________
Delphi mailing list
[EMAIL PROTECTED]
http://ns3.123.co.nz/mailman/listinfo/delphi
