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

[Karsten Hilbert]

On Fri, Feb 19, 2010 at 03:12:08AM -0800, Jim Busser wrote:

> because it wants only a single record defining the drug's availability, 
> whereas Health Canada data provides a historical record of notices. 
> Accordingly there exist multiple values of
> 
>       CURRENT_STATUS_FLAG
> 
> with varying ages of records, per drug. Presumably I need to make the SELECT 
> command ignore values *except* for the newest per drug else some way to first 
> delete the non-newest records per drug.

Ah, now I understand. This is typically done with either a
GROUP BY/HAVING or a self-join.

select
        *
FROM
        this_table as level1
WHERE
        level1.date = (select max(date) from this_table as level2 where 
level1.drug_code = level2.drug_code)
                and
        ... other conditions ...
;

Does that help ?

Karsten
-- 
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346