Select x.SINSEIKUBUN, x.NO, x.HIDUKEF From KINMUJYOKYO x, (Select rownum rnum, No From (Select No, Max(HIDUKEF) MaxHIDUKEF From KINMUJYOKYO Group by No) Order by MaxHIDUKEF DESC) y Where x.No = y.No Order By y.rnum, x.HIDUKEF;
sorry, missed DESC at last: Select x.SINSEIKUBUN, x.NO, x.HIDUKEF From KINMUJYOKYO x, (Select rownum rnum, No From (Select No, Max(HIDUKEF) MaxHIDUKEF From KINMUJYOKYO Group by No) Order by MaxHIDUKEF DESC) y Where x.No = y.No Order By y.rnum, x.HIDUKEF DESC;
再麻烦一下Select No, Max(HIDUKEF) MaxHIDUKEF From KK_KINMUJYOKYO_WK Group by NoSINSEIKUBUN和No共同作为主键的,当SINSEIKUBUN是7或8时,应当Group by No 当SINSEIKUBUN不是7或8时,应当Group by SINSEIKUBUN,No 我想实现当SINSEIKUBUN是7或8 并且NO相同的记录放在一起,这怎么办那? 谢谢
Select x.SINSEIKUBUN, x.NO, x.HIDUKEF From KINMUJYOKYO x, (Select rownum rnum, SINSEIKUBUN, No From (Select decode(SINSEIKUBUN,8,7,SINSEIKUBUN) SINSEIKUBUN, No, Max(HIDUKEF) MaxHIDUKEF From KINMUJYOKYO Group by decode(SINSEIKUBUN,8,7,SINSEIKUBUN), No) Order by MaxHIDUKEF DESC, No, SINSEIKUBUN) y Where x.No = y.No and decode(x.SINSEIKUBUN,8,7,x.SINSEIKUBUN) = y.SINSEIKUBUN Order By y.rnum, x.HIDUKEF DESC;
select ... from ... where ... order by no,workfield
SINSEIKUBUN NOT NULL,
NO NOT NULL,
HIDUKEF NULL,
PRIMARY KEY(SINSEIKUBUN,NO)
)部分记录'
SINSEIKUBUN NO HIDUKEF
1 896 2002/10/01
6 147 2002/09/21
2 126 2002/10/04
3 132 2002/10/078 182 2002/10/17
8 182
7 182 2002/10/17
7 182 2002/09/303 133 2002/10/08
5 133 2002/10/04
5 134 2002/09/26
4 127 2002/09/30
4 128 2002/09/10客户希望按HIDUKEF排序,
同时SINSEIKUBUN是7和8且NO相同的4条记录要排在一起
这四条记录至少有一条不是NULL如果先按NO排序,就不能保障HIDUKEF的顺序
SINSEIKUBUN NO HIDUKEF
7 182 2002/10/17
7 182 2002/9/30
8 182 2002/10/17
8 182
3 133 2002/10/8
3 132 2002/10/7
2 126 2002/10/4
5 133 2002/10/4
1 896 2002/10/1
4 127 2002/9/30
5 134 2002/9/26
6 147 2002/9/21
4 128 2002/9/10因为NO是182的4条记录中最大HIDUKEF是2002/10/17
所以把4条放在最前面,其余按HIDUKEF从大到小排列就行了
如果直接按HIDUKEF排序,NO是182的4条记录必然是不在一起的
现在希望排在一起,不知我说明白没有谢谢
From KINMUJYOKYO x,
(Select rownum rnum, No
From (Select No, Max(HIDUKEF) MaxHIDUKEF
From KINMUJYOKYO
Group by No)
Order by MaxHIDUKEF DESC) y
Where x.No = y.No
Order By y.rnum, x.HIDUKEF;
Select x.SINSEIKUBUN, x.NO, x.HIDUKEF
From KINMUJYOKYO x,
(Select rownum rnum, No
From (Select No, Max(HIDUKEF) MaxHIDUKEF
From KINMUJYOKYO
Group by No)
Order by MaxHIDUKEF DESC) y
Where x.No = y.No
Order By y.rnum, x.HIDUKEF DESC;
From KK_KINMUJYOKYO_WK
Group by NoSINSEIKUBUN和No共同作为主键的,当SINSEIKUBUN是7或8时,应当Group by No
当SINSEIKUBUN不是7或8时,应当Group by SINSEIKUBUN,No
我想实现当SINSEIKUBUN是7或8 并且NO相同的记录放在一起,这怎么办那?
谢谢
SINSEIKUBUN NO HIDUKEF
7 182 2002/10/17
7 182 2002/9/30
8 182 2002/10/17
8 182
这样的纪录?
From KINMUJYOKYO x,
(Select rownum rnum, SINSEIKUBUN, No
From (Select decode(SINSEIKUBUN,8,7,SINSEIKUBUN) SINSEIKUBUN,
No, Max(HIDUKEF) MaxHIDUKEF
From KINMUJYOKYO
Group by decode(SINSEIKUBUN,8,7,SINSEIKUBUN), No)
Order by MaxHIDUKEF DESC, No, SINSEIKUBUN) y
Where x.No = y.No
and decode(x.SINSEIKUBUN,8,7,x.SINSEIKUBUN) = y.SINSEIKUBUN
Order By y.rnum, x.HIDUKEF DESC;