一个筛选和排序的小问题

[guanyelong]

FILMNO FILENAME FEATUREDATE FEATURETIME
00117107 绑架冰激凌（数字） 2011-07-10 23:40
00117174 追鱼（数字） 2011-07-10 23:35
00116041 建国大业 2011-07-10 23:25
00117107 绑架冰激凌（数字） 2011-07-10 23:20
00117174 追鱼（数字） 2011-07-10 21:55
00117107 绑架冰激凌（数字） 2011-07-10 20:35
00117107 绑架冰激凌（数字 ） 2011-07-10 20:20
00116041 建国大业 2011-07-10 19:40
00117174 追鱼（数字） 2011-07-10 19:35
00117174 追鱼（数字） 2011-07-10 18:45
00117155 天秤（数字） 2011-07-10 18:15
00117107 绑架冰激凌（数字） 2011-07-10 16:50
00116041 建国大业 2011-07-10 16:35
05110514 阿凡达 2011-07-10 16:25
00117107 绑架冰激凌（数字） 2011-07-10 16:05
00117164 天下太忙（数字） 2011-07-10 15:50
00114520 落叶归根（数字） 2011-07-10 14:25
我的sql
select distinct f.filmno,f.filmname, f.featuredate,f.featuretime
from t0201_feature_app f
where 1=1 AND F.FEATUREDATE between '2011-07-10' and '2011-07-20' and f.appcode='TEST' order by f.featuredate,f.featuretime desc
我想让FILMNO FILENAME这列 不重复且能根据 featuredate和featuretime 排序 该怎么办呢

[BlueskyWide]

--试一下：

select filmno, filmname, min(featuredate), min(featuretime)
  from t0201_feature_app
 where FEATUREDATE between '2011-07-10' and '2011-07-20'
 group by filmno
 order by featuredate, featuretime desc

[秋雨飘落]

select f.filmno, f.filmname, f.featuredate, f.featuretime
from t0201_feature_app f
where
rowid = (select max(rowid) from t0201_feature_app b where f.filmno = b.filmno and f.filmname = b.filmname )

AND F.FEATUREDATE between '2011-07-10' and '2011-07-20'
and f.appcode = 'TEST'
order by f.featuredate,
f.featuretime desc

[guanyelong]

2L 可以
1L 的需要分组

[guanyelong]

2L 你的rowid = (select max(rowid) from t0201_feature_app b where f.filmno = b.filmno and f.filmname = b.filmname 是什么意思啊 解释一下呗

[guanyelong]

[Quote=引用 2 楼 zftang 的回复:]
select f.filmno, f.filmname, f.featuredate, f.featuretime
from t0201_feature_app f
where
rowid = (select max(rowid) from t0201_feature_app b where f.filmno = b.filmno and f.filmname = b.filmn……
[/Quote]
你的rowid = (select max(rowid) from t0201_feature_app b where f.filmno = b.filmno and f.filmname = b.filmname 是什么意思啊 解释一下呗

[秋雨飘落]

[Quote=引用 5 楼 guanyelong 的回复:]
引用 2 楼 zftang 的回复:
select f.filmno, f.filmname, f.featuredate, f.featuretime
from t0201_feature_app f
where
rowid = (select max(rowid) from t0201_feature_app b where f.filmno = b.filmno and f.film……
[/Quote]
rowID是ORACLE的伪列，相同的2个字段，只取ROWID最大的一条，也就是所谓的去重复，不明白看我空间加我Q;

[guanyelong]

[Quote=引用 6 楼 zftang 的回复:]
引用 5 楼 guanyelong 的回复:
引用 2 楼 zftang 的回复:
select f.filmno, f.filmname, f.featuredate, f.featuretime
from t0201_feature_app f
where
rowid = (select max(rowid) from t0201_feature_app b where f.film……
[/Quote]
不好使 嗯 少了

[guanyelong]

[Quote=引用 1 楼 blueskywide 的回复:]
--试一下：


SQL code

select filmno, filmname, min(featuredate), min(featuretime)
from t0201_feature_app
where FEATUREDATE between '2011-07-10' and '2011-07-20'
group by filmno
order by fea……
[/Quote]
不行

[guanyelong]

解决了 嘿嘿

select filmno, filmname,min(rnum) r from 
(select filmno, filmname,rownum rnum
  from (select filmno, filmname, featuredate, featuretime
          from t0201_feature_app where featuredate between '2009-10-20' and '2009-10-25'
         order by featuredate desc, featuretime desc)
         ) t
group by filmno, filmname
order by r asc