select * from table1 join (select id, max(qty) as qty from table1 group by id) table2 on table1.qty = table2.qtyyou need to be sure that qty is unique per names; otherwise you'll need another sub-query on max(date) or whatever.
> CREATE CURSOR table1 (id int,names c(10),date d,qty int) > INSERT INTO table1 VALUES (1,"ADMIN",{^2017-02-19},25) > INSERT INTO table1 VALUES (1,"ADMIN",{^2017-02-20},21) > > INSERT INTO table1 VALUES (2,"GENERAL",{^2017-02-17},29) > INSERT INTO table1 VALUES (2,"GENERAL",{^2017-02-21},14) > > SELECT id,MAX(names),MAX(date),MAX(qty)from table1 GROUP BY id > > brow >>