John


>Why don't you select into a memory table, and then select distinct from the memory table?

Mainly because it wouldn't work

The reason is quite simple. Regardless of wether I do a SELECT or a SELECT INTO the data is the same. The various columns I'll let you work out from the sql below but for DISTINCT to work every field in the row has to be the same as another row. In this case whilst _Forname and _Surname would be duplicated and hence if that was all I was selecting distinct would work BUT (and its a big but) _Left (the year someone left a job) is pretty much to be unique for a person as is a _JobTitle. So you might get 2 Fred Bloggs both the same person, but with leaving dates of 2001 and null. Ignoring padding and field delimiters etc you'd get FredBloggs and FredBloggs2004 - obviously not the same and hence DISTINCT will not filter either out.

>
>Just a thought from a novice...

That's why I've explained in some detail - hope it helps.

Roy Lambert

"John" <eydunl@post.olivant.fo> wrote in message
news:F48A478E-0367-4870-841A-4A2EF23F623A@news.elevatesoft.com...
> Why don't you select into a memory table, and then select distinct from
> the memory table?
>
> Just a thought from a novice...

Close, but no ceegar. Selecting INTO adds nothing, since it won't change the
table. And distinct requires that all fields be identical.

Try something like this (tables have been simplified, but you get the idea)

CREATE TABLE IF NOT EXISTS "t1"
(
  "id" INTEGER,
  "name" VARCHAR(10),
PRIMARY KEY ("RecordID") COMPRESS NONE
LOCALE CODE 0
USER MAJOR VERSION 1
);


INSERT INTO "t1" VALUES (1,
        'one');
INSERT INTO "t1" VALUES (2,
        'two');
INSERT INTO "t1" VALUES (3,
        'three');

CREATE TABLE IF NOT EXISTS "t2"
(
  "id" INTEGER,
  "dt" INTEGER,
PRIMARY KEY ("RecordID") COMPRESS NONE
LOCALE CODE 0
USER MAJOR VERSION 1
);


INSERT INTO "t2" VALUES (1, 100);
INSERT INTO "t2" VALUES (3, 300);
INSERT INTO "t2" VALUES (1, 101);



select max(dt) hidt, name, t1.id
from t2
right outer join t1 on t1.id = t2.id
group by t1.id


Robert