oracle只查每个人（相同人名过滤），添加动作的时间in_time减少动作的时间out_time最晚的数据

Question

例如有表A，A有4个字段
          name     in_time                 out_time                 total
数据如下 
          user1    2011-12-1 18:01:00                                    20 
         user1    2012-01-02 12:00:00                                 10
          user2    2011-01-02 13:00:00                                 30
         user3                              2011-05-06 18:00:00        20
         user3                              2012-05-09 18:00:00        30
查询的结果为：
        user1    2012-01-02 12:00:00                                 10
       user2    2011-01-02 13:00:00                                 30
       user3                             2012-05-09 18:00:00        30
oracle

Answer 1

select *  from aaa WHERE (name) IN ( 
       SELECT name FROM aaa GROUP BY name HAVING COUNT(name) >= 1) AND ROWID NOT IN (
       SELECT MIN(ROWID) FROM aaa GROUP BY name HAVING COUNT(*) > 1
       );