Query to Select Maximum Age

[johnds]

Another basic question from newbie. I have a 10 million record database
of patient contact with their physician. The original file contained
all thier clinical information and personal characteristics . Each
patient record was repeated for each visit. I only want a table of
unique records for each patient with their characteristics , simple
enough with select distinct, but I found that I obtained duplicate
records if the patient visited their doctor between birthdays. I have
decided to take the record with their max age as more current. The
following however did not work, any suggestions?

SELECT DISTINCT ptnum, age, sex, address
FROM ptdb
GROUP BY ptnum, age, sex, address
HAVING (max(age));

[ManningFan]

Try using a Group By query instead of using DISTINCT. Group By ptnum,
sex, address, MAX of age. That should give you one record per patient,
unless your data is screwy.

johnds wrote:

Another basic question from newbie. I have a 10 million record database
of patient contact with their physician. The original file contained
all thier clinical information and personal characteristics . Each
patient record was repeated for each visit. I only want a table of
unique records for each patient with their characteristics , simple
enough with select distinct, but I found that I obtained duplicate
records if the patient visited their doctor between birthdays. I have
decided to take the record with their max age as more current. The
following however did not work, any suggestions?

SELECT DISTINCT ptnum, age, sex, address
FROM ptdb
GROUP BY ptnum, age, sex, address
HAVING (max(age));

[johnds]

Running the query now, takes a very long time. max of age does not work
in the Group By clause, so the query running now is:
SELECT ptnum, age, sex, address
FROM ptdb
GROUP BY ptnum, age, sex, address
HAVING (max(age));

Should this work?

ManningFan wrote:

Try using a Group By query instead of using DISTINCT. Group By ptnum,
sex, address, MAX of age. That should give you one record per patient,
unless your data is screwy.

johnds wrote:

Another basic question from newbie. I have a 10 million record database
of patient contact with their physician. The original file contained
all thier clinical information and personal characteristics . Each
patient record was repeated for each visit. I only want a table of
unique records for each patient with their characteristics , simple
enough with select distinct, but I found that I obtained duplicate
records if the patient visited their doctor between birthdays. I have
decided to take the record with their max age as more current. The
following however did not work, any suggestions?

SELECT DISTINCT ptnum, age, sex, address
FROM ptdb
GROUP BY ptnum, age, sex, address
HAVING (max(age));

[johnds]

To close the loop on this one, the code does not work, data is fine. I
tried different ways, still got records with both ages. Eventually
exported to SPSS and used aggregate functions and max(age), worked
first time.
johnds wrote:

Running the query now, takes a very long time. max of age does not work
in the Group By clause, so the query running now is:
SELECT ptnum, age, sex, address
FROM ptdb
GROUP BY ptnum, age, sex, address
HAVING (max(age));

Should this work?

ManningFan wrote:

Try using a Group By query instead of using DISTINCT. Group By ptnum,
sex, address, MAX of age. That should give you one record per patient,
unless your data is screwy.

johnds wrote:

Another basic question from newbie. I have a 10 million record database
of patient contact with their physician. The original file contained
all thier clinical information and personal characteristics . Each
patient record was repeated for each visit. I only want a table of
unique records for each patient with their characteristics , simple
enough with select distinct, but I found that I obtained duplicate
records if the patient visited their doctor between birthdays. I have
decided to take the record with their max age as more current. The
following however did not work, any suggestions?
>
SELECT DISTINCT ptnum, age, sex, address
FROM ptdb
GROUP BY ptnum, age, sex, address
HAVING (max(age));