>I have a SQL that extract a specific set of datas from a table. One of my field extracted can occur more than once. I would like my SQL to only extract unique occuring of that field. So, this bring us to SELECT * FROM table GROUP BY field1.
>
>But, I would like to be able to only keep the occurence of field1 which contain the highest date. Is there a syntax that will allow me to do something like this: SELECT * FROM table GROUP BY 1 ORDER BY date DESCENDING KEEP THE HIGHEST ONE.
>
>I think you got the point. :)
>
>Any idea?

Hi Mike,

They really should have a KEEP THE HIGHEST ONE clause
on the SELECT SQL. I guess they don't because they
have enough trouble getting the same results twice
with the clauses currently supported.

You need to do kind of a self-relation /self-join.

* --- Just to keep the FROM clause short!
#DEFINE TASFILE c:\vfp\samples\mainsamp\data\orders

SELECT MAX(b.order_date) AS HighDate, a.* ;
FROM TASFILE a, TASFILE b ;
WHERE a.order_number = b.order_number ;
HAVING a.order_number = b.order_number ;
GROUP BY a.customer_id ;
ORDER BY a.customer_id ;
INTO CURSOR NewOrders

Critics?


Chris