Re: [Gnumed-devel] Getting Canadian drug data into FreeDiams

[Jim Busser]

Tried a couple of things based on problem postings, but none work:


SELECT DISTINCT DRUG_CODE, CURRENT_STATUS_FLAG, STATUS, MAX(HISTORY_DATE)
FROM status
GROUP BY DRUG_CODE;

yields 17,523 rows among which the maximum appears to be calculated from left 
character values "31-DEC-YYYY"

*********************

SELECT a.CURRENT_STATUS_FLAG, a.STATUS , a.DRUG_CODE, a.HISTORY_DATE 
      FROM status a, (SELECT DRUG_CODE, MAX(HISTORY_DATE) AS max_date FROM 
status GROUP BY DRUG_CODE) b
      WHERE a.DRUG_CODE = b.DRUG_CODE AND a.HISTORY_DATE = b.max_date;

after ~ 10 minutes, returns 17,791 records among which drug_code 2059 shows 
history_date 31-Dec-1975 instead of the desired 05-Oct-2009

*********************

SELECT DRUG_CODE, CURRENT_STATUS_FLAG, STATUS, HISTORY_DATE
FROM status as A
WHERE HISTORY_DATE =
(SELECT MAX(B.HISTORY_DATE)
FROM status as B
WHERE A.DRUG_CODE = B.DRUG_CODE);

after ~ 15 minutes, returns the same