如何过滤数据为同一个公司的最新一条货物？

Question

比如我有这样的数据：公司名  货物    创建时间
公司A   货物1   2010-05-10 14:20
公司A   货物2   2010-05-10 14:20
公司B   货物3   2010-05-10 14:30
公司B   货物4   2010-05-10 14:32
公司C   货物5   2010-05-10 15:03   
我想做个视图只显示一个公司最新的一条货物，同一个创建时间的只能取其中一条
即：
公司A   货物1   2010-05-10 14:20（或货物2）
公司B   货物3   2010-05-10 14:30 
公司C   货物5   2010-05-10 15:03我现在麻烦就在如果同一个公司的货物时间是同样的时候解决不了
请教大家怎么解决？

Answer 1

so easy,过滤重复行的问题，按时间取最大就可以了，因为你的时间还有重复相同的，那么就按照时间和rowid取最大就ok了

Answer 2

没有看到是时间相同的保留一条，ok,as below:
SQL> select * from goods;
 
COM_NAME                                                               GOODS_NAME                                                           CREATE_TIME
-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -----------
公司A                                                                         货物1                                                                         2010/5/10
公司A                                                                         货物2                                                                         2010/5/10
公司B                                                                         货物3                                                                         2010/5/10
公司B                                                                         货物4                                                                         2010/5/11
公司C                                                                         货物5                                                                         2010/5/10
 
SQL> 
SQL> select * from goods a where a.rowid=(select max(b.rowid) from goods b where a.com_name=b.com_name
  2  and a.create_time=b.create_time);
 
COM_NAME                                                               GOODS_NAME                                                           CREATE_TIME
-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -----------
公司A                                                                         货物2                                                                         2010/5/10
公司B                                                                         货物3                                                                         2010/5/10
公司B                                                                         货物4                                                                         2010/5/11
公司C                                                                         货物5                                                                         2010/5/10

Answer 3

而且数据来源是个视图，不知能不能保证rowid的顺序啊

Answer 4

SELECT 公司名,货物,创建时间
FROM TAB
WHERE (公司名,创建时间) IN
(SELECT 公司名 ,MAX(创建时间)
FROM TAB
GROUP BY 公司名
)
AND ROWNUM=1

Answer 5

create view v_tabke as 
select 公司名,货物,创建时间 from table a 
where exists(select 1 from table where a.公司名=公司名 and a.创建时间>创建时间)create view v_tabke as 
select 公司名,货物,创建时间 from table a 
where 创建时间 in(select max(创建时间) from table where a.公司名=公司名)

Answer 6

我想做个视图只显示一个公司最新的一条货物，同一个创建时间的只能取其中一条没有看清楚，我以为只是时间相同的取一条呢？满足你的需求很简单嘛SQL> select * from goods order by com_name;
 
COM_NAME                                                               GOODS_NAME                                                           CREATE_TIME
-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -----------
公司A                                                                         货物1                                                                         2010/5/10
公司A                                                                         货物2                                                                         2010/5/10
公司A                                                                         货物6                                                                         2010/5/9
公司B                                                                         货物4                                                                         2010/5/11
公司B                                                                         货物3                                                                         2010/5/10
公司C                                                                         货物5                                                                         2010/5/10
 
6 rows selected
 
SQL> 
SQL> select  com_name,goods_name,create_time from (
  2  select com_name,goods_name,create_time,row_number() over(partition by com_name order by create_time desc) rn from goods
  3  )
  4  where rn=1;
 
COM_NAME                                                               GOODS_NAME                                                           CREATE_TIME
-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -----------
公司A                                                                         货物1                                                                         2010/5/10
公司B                                                                         货物4                                                                         2010/5/11
公司C                                                                         货物5                                                                         2010/5/10

Answer 7

SELECT *
  FROM table1 a
 WHERE NOT EXISTS (SELECT 1
          FROM table1 b
         WHERE b.公司名 = a.公司名 AND
               b.创建时间 >= a.创建时间 AND
               b.rowid > a.rowid);