when i code
select
row_.*, rownum rownum_
from
(select
topic0_.id as id6_, topic0_.title as title6_,
topic0_.publish_author_id as publish3_6_, topic0_.reply_number as reply4_6_,
topic0_.read_number as read5_6_, topic0_.publish_author_name as publish6_6_,
topic0_.publish_time as publish7_6_, topic0_.last_reply_time as last8_6_,
topic0_.last_reply_name as last9_6_, topic0_.main_text as main10_6_, topic0_.FORUM_ID as FORUM11_6_
from topic topic0_
where topic0_.FORUM_ID='16F37E59FE494AC9B17A2340387335EF'
order by
case when topic0_.last_reply_time is not null then topic0_.last_reply_time
else topic0_.publish_time end desc
) row_;
the result:
ID6_ TITLE6_ PUBLISH3_6_ REPLY4_6_ READ5_6_ PUBLISH6_6_ PUBLISH7_6_ LAST8_6_ LAST9_6_ MAIN10_6_ FORUM11_6_ ROWNUM_
1 402840713e108ea4013e1090c95c0001 trytr 2 2 0 ss 2013-4-16 9:58:44 2013-4-16 9:59:21 ss fdsfds 16F37E59FE494AC9B17A2340387335EF 1
2 402840713e108ea4013e1090127e0000 trytr 2 0 0 ss 2013-4-16 9:57:57 ytrytr 16F37E59FE494AC9B17A2340387335EF 2
3 402840713e0d3616013e0d3618c20000 cde0 2 0 0 ss 2013-4-15 18:20:49 abc0 16F37E59FE494AC9B17A2340387335EF 3
4 402840713e0d3616013e0d3618da0063 cde99 2 0 0 ss 2013-4-15 18:20:49 abc99 16F37E59FE494AC9B17A2340387335EF 4
5 402840713e0d3616013e0d3618da0062 cde98 2 0 0 ss 2013-4-15 18:20:49 abc98 16F37E59FE494AC9B17A2340387335EF 5
6 402840713e0d3616013e0d3618da0061 cde97 2 0 0 ss 2013-4-15 18:20:49 abc97 16F37E59FE494AC9B17A2340387335EF 6
7 402840713e0d3616013e0d3618da0060 cde96 2 0 0 ss 2013-4-15 18:20:49 abc96 16F37E59FE494AC9B17A2340387335EF 7
8 402840713e0d3616013e0d3618da005f cde95 2 0 0 ss 2013-4-15 18:20:49 abc95 16F37E59FE494AC9B17A2340387335EF 8
9 402840713e0d3616013e0d3618da005e cde94 2 0 0 ss 2013-4-15 18:20:49 abc94 16F37E59FE494AC9B17A2340387335EF 9
10 402840713e0d3616013e0d3618da005d cde93 2 0 0 ss 2013-4-15 18:20:49 abc93 16F37E59FE494AC9B17A2340387335EF 10
11 402840713e0d3616013e0d3618da005c cde92 2 0 0 ss 2013-4-15 18:20:49 abc92 16F37E59FE494AC9B17A2340387335EF 11
12 402840713e0d3616013e0d3618da005b cde91 2 0 0 ss 2013-4-15 18:20:49 abc91 16F37E59FE494AC9B17A2340387335EF 12
13 402840713e0d3616013e0d3618d9005a cde90 2 0 0 ss 2013-4-15 18:20:49 abc90 16F37E59FE494AC9B17A2340387335EF 13
14 402840713e0d3616013e0d3618d90059 cde89 2 0 0 ss 2013-4-15 18:20:49 abc89 16F37E59FE494AC9B17A2340387335EF 14
15 402840713e0d3616013e0d3618d90058 cde88 2 0 0 ss 2013-4-15 18:20:49 abc88 16F37E59FE494AC9B17A2340387335EF 15
16 402840713e0d3616013e0d3618d90057 cde87 2 0 0 ss 2013-4-15 18:20:49 abc87 16F37E59FE494AC9B17A2340387335EF 16
17 402840713e0d3616013e0d3618d90056 cde86 2 0 0 ss 2013-4-15 18:20:49 abc86 16F37E59FE494AC9B17A2340387335EF 17
18 402840713e0d3616013e0d3618d90055 cde85 2 0 0 ss 2013-4-15 18:20:49 abc85 16F37E59FE494AC9B17A2340387335EF 18
19 402840713e0d3616013e0d3618d90054 cde84 2 0 0 ss 2013-4-15 18:20:49 abc84 16F37E59FE494AC9B17A2340387335EF 19
20 402840713e0d3616013e0d3618d90053 cde83 2 0 0 ss 2013-4-15 18:20:49 abc83 16F37E59FE494AC9B17A2340387335EF 20
21 402840713e0d3616013e0d3618d90052 cde82 2 0 0 ss 2013-4-15 18:20:49 abc82 16F37E59FE494AC9B17A2340387335EF 21
22 402840713e0d3616013e0d3618d90051 cde81 2 0 0 ss 2013-4-15 18:20:49 abc81 16F37E59FE494AC9B17A2340387335EF 22
23 402840713e0d3616013e0d3618d80050 cde80 2 0 0 ss 2013-4-15 18:20:49 abc80 16F37E59FE494AC9B17A2340387335EF 23
24 402840713e0d3616013e0d3618d8004f cde79 2 0 0 ss 2013-4-15 18:20:49 abc79 16F37E59FE494AC9B17A2340387335EF 24
25 402840713e0d3616013e0d3618d8004e cde78 2 0 0 ss 2013-4-15 18:20:49 abc78 16F37E59FE494AC9B17A2340387335EF 25
26 402840713e0d3616013e0d3618d8004d cde77 2 0 0 ss 2013-4-15 18:20:49 abc77 16F37E59FE494AC9B17A2340387335EF 26
27 402840713e0d3616013e0d3618d8004c cde76 2 0 0 ss 2013-4-15 18:20:49 abc76 16F37E59FE494AC9B17A2340387335EF 27
28 402840713e0d3616013e0d3618d8004b cde75 2 0 0 ss 2013-4-15 18:20:49 abc75 16F37E59FE494AC9B17A2340387335EF 28
29 402840713e0d3616013e0d3618d8004a cde74 2 0 0 ss 2013-4-15 18:20:49 abc74 16F37E59FE494AC9B17A2340387335EF 29
30 402840713e0d3616013e0d3618d80049 cde73 2 0 0 ss 2013-4-15 18:20:49 abc73 16F37E59FE494AC9B17A2340387335EF 30
31 402840713e0d3616013e0d3618d80048 cde72 2 0 0 ss 2013-4-15 18:20:49 abc72 16F37E59FE494AC9B17A2340387335EF 31
32 402840713e0d3616013e0d3618d70047 cde71 2 0 0 ss 2013-4-15 18:20:49 abc71 16F37E59FE494AC9B17A2340387335EF 32
33 402840713e0d3616013e0d3618d70046 cde70 2 0 0 ss 2013-4-15 18:20:49 abc70 16F37E59FE494AC9B17A2340387335EF 33
34 402840713e0d3616013e0d3618d70045 cde69 2 0 0 ss 2013-4-15 18:20:49 abc69 16F37E59FE494AC9B17A2340387335EF 34
35 402840713e0d3616013e0d3618d70044 cde68 2 0 0 ss 2013-4-15 18:20:49 abc68 16F37E59FE494AC9B17A2340387335EF 35
36 402840713e0d3616013e0d3618d70043 cde67 2 0 0 ss 2013-4-15 18:20:49 abc67 16F37E59FE494AC9B17A2340387335EF 36
37 402840713e0d3616013e0d3618d70042 cde66 2 0 0 ss 2013-4-15 18:20:49 abc66 16F37E59FE494AC9B17A2340387335EF 37
38 402840713e0d3616013e0d3618d70041 cde65 2 0 0 ss 2013-4-15 18:20:49 abc65 16F37E59FE494AC9B17A2340387335EF 38
39 402840713e0d3616013e0d3618d70040 cde64 2 0 0 ss 2013-4-15 18:20:49 abc64 16F37E59FE494AC9B17A2340387335EF 39
40 402840713e0d3616013e0d3618d7003f cde63 2 0 0 ss 2013-4-15 18:20:49 abc63 16F37E59FE494AC9B17A2340387335EF 40
41 402840713e0d3616013e0d3618d7003e cde62 2 0 0 ss 2013-4-15 18:20:49 abc62 16F37E59FE494AC9B17A2340387335EF 41
42 402840713e0d3616013e0d3618d6003d cde61 2 0 0 ss 2013-4-15 18:20:49 abc61 16F37E59FE494AC9B17A2340387335EF 42
43 402840713e0d3616013e0d3618d6003c cde60 2 0 0 ss 2013-4-15 18:20:49 abc60 16F37E59FE494AC9B17A2340387335EF 43
44 402840713e0d3616013e0d3618d6003b cde59 2 0 0 ss 2013-4-15 18:20:49 abc59 16F37E59FE494AC9B17A2340387335EF 44
45 402840713e0d3616013e0d3618d6003a cde58 2 0 0 ss 2013-4-15 18:20:49 abc58 16F37E59FE494AC9B17A2340387335EF 45
46 402840713e0d3616013e0d3618d60039 cde57 2 0 0 ss 2013-4-15 18:20:49 abc57 16F37E59FE494AC9B17A2340387335EF 46
47 402840713e0d3616013e0d3618d60038 cde56 2 0 0 ss 2013-4-15 18:20:49 abc56 16F37E59FE494AC9B17A2340387335EF 47
48 402840713e0d3616013e0d3618d60037 cde55 2 0 0 ss 2013-4-15 18:20:49 abc55 16F37E59FE494AC9B17A2340387335EF 48
49 402840713e0d3616013e0d3618d60036 cde54 2 0 0 ss 2013-4-15 18:20:49 abc54 16F37E59FE494AC9B17A2340387335EF 49
50 402840713e0d3616013e0d3618d60035 cde53 2 0 0 ss 2013-4-15 18:20:49 abc53 16F37E59FE494AC9B17A2340387335EF 50
51 402840713e0d3616013e0d3618d50034 cde52 2 0 0 ss 2013-4-15 18:20:49 abc52 16F37E59FE494AC9B17A2340387335EF 51
52 402840713e0d3616013e0d3618d50033 cde51 2 0 0 ss 2013-4-15 18:20:49 abc51 16F37E59FE494AC9B17A2340387335EF 52
53 402840713e0d3616013e0d3618d50032 cde50 2 0 0 ss 2013-4-15 18:20:49 abc50 16F37E59FE494AC9B17A2340387335EF 53
54 402840713e0d3616013e0d3618d50031 cde49 2 0 0 ss 2013-4-15 18:20:49 abc49 16F37E59FE494AC9B17A2340387335EF 54
55 402840713e0d3616013e0d3618d50030 cde48 2 0 0 ss 2013-4-15 18:20:49 abc48 16F37E59FE494AC9B17A2340387335EF 55
56 402840713e0d3616013e0d3618d5002f cde47 2 0 0 ss 2013-4-15 18:20:49 abc47 16F37E59FE494AC9B17A2340387335EF 56
57 402840713e0d3616013e0d3618d5002e cde46 2 0 0 ss 2013-4-15 18:20:49 abc46 16F37E59FE494AC9B17A2340387335EF 57
58 402840713e0d3616013e0d3618d5002d cde45 2 0 0 ss 2013-4-15 18:20:49 abc45 16F37E59FE494AC9B17A2340387335EF 58
59 402840713e0d3616013e0d3618d5002c cde44 2 0 0 ss 2013-4-15 18:20:49 abc44 16F37E59FE494AC9B17A2340387335EF 59
60 402840713e0d3616013e0d3618d5002b cde43 2 0 0 ss 2013-4-15 18:20:49 abc43 16F37E59FE494AC9B17A2340387335EF 60
61 402840713e0d3616013e0d3618d5002a cde42 2 0 0 ss 2013-4-15 18:20:49 abc42 16F37E59FE494AC9B17A2340387335EF 61
62 402840713e0d3616013e0d3618d40029 cde41 2 0 0 ss 2013-4-15 18:20:49 abc41 16F37E59FE494AC9B17A2340387335EF 62
63 402840713e0d3616013e0d3618d40028 cde40 2 0 0 ss 2013-4-15 18:20:49 abc40 16F37E59FE494AC9B17A2340387335EF 63
64 402840713e0d3616013e0d3618d40027 cde39 2 0 0 ss 2013-4-15 18:20:49 abc39 16F37E59FE494AC9B17A2340387335EF 64
65 402840713e0d3616013e0d3618d40026 cde38 2 0 0 ss 2013-4-15 18:20:49 abc38 16F37E59FE494AC9B17A2340387335EF 65
66 402840713e0d3616013e0d3618d40025 cde37 2 0 0 ss 2013-4-15 18:20:49 abc37 16F37E59FE494AC9B17A2340387335EF 66
67 402840713e0d3616013e0d3618d40024 cde36 2 0 0 ss 2013-4-15 18:20:49 abc36 16F37E59FE494AC9B17A2340387335EF 67
68 402840713e0d3616013e0d3618d40023 cde35 2 0 0 ss 2013-4-15 18:20:49 abc35 16F37E59FE494AC9B17A2340387335EF 68
69 402840713e0d3616013e0d3618d40022 cde34 2 0 0 ss 2013-4-15 18:20:49 abc34 16F37E59FE494AC9B17A2340387335EF 69
70 402840713e0d3616013e0d3618d30021 cde33 2 0 0 ss 2013-4-15 18:20:49 abc33 16F37E59FE494AC9B17A2340387335EF 70
71 402840713e0d3616013e0d3618d30020 cde32 2 0 0 ss 2013-4-15 18:20:49 abc32 16F37E59FE494AC9B17A2340387335EF 71
72 402840713e0d3616013e0d3618d3001f cde31 2 0 0 ss 2013-4-15 18:20:49 abc31 16F37E59FE494AC9B17A2340387335EF 72
73 402840713e0d3616013e0d3618d3001e cde30 2 0 0 ss 2013-4-15 18:20:49 abc30 16F37E59FE494AC9B17A2340387335EF 73
74 402840713e0d3616013e0d3618d3001d cde29 2 0 0 ss 2013-4-15 18:20:49 abc29 16F37E59FE494AC9B17A2340387335EF 74
75 402840713e0d3616013e0d3618d3001c cde28 2 0 0 ss 2013-4-15 18:20:49 abc28 16F37E59FE494AC9B17A2340387335EF 75
76 402840713e0d3616013e0d3618d3001b cde27 2 0 0 ss 2013-4-15 18:20:49 abc27 16F37E59FE494AC9B17A2340387335EF 76
77 402840713e0d3616013e0d3618d3001a cde26 2 0 0 ss 2013-4-15 18:20:49 abc26 16F37E59FE494AC9B17A2340387335EF 77
78 402840713e0d3616013e0d3618d30019 cde25 2 0 0 ss 2013-4-15 18:20:49 abc25 16F37E59FE494AC9B17A2340387335EF 78
79 402840713e0d3616013e0d3618d30018 cde24 2 0 0 ss 2013-4-15 18:20:49 abc24 16F37E59FE494AC9B17A2340387335EF 79
80 402840713e0d3616013e0d3618d20017 cde23 2 0 0 ss 2013-4-15 18:20:49 abc23 16F37E59FE494AC9B17A2340387335EF 80
81 402840713e0d3616013e0d3618d20016 cde22 2 0 0 ss 2013-4-15 18:20:49 abc22 16F37E59FE494AC9B17A2340387335EF 81
82 402840713e0d3616013e0d3618d20015 cde21 2 0 0 ss 2013-4-15 18:20:49 abc21 16F37E59FE494AC9B17A2340387335EF 82
83 402840713e0d3616013e0d3618d20014 cde20 2 0 0 ss 2013-4-15 18:20:49 abc20 16F37E59FE494AC9B17A2340387335EF 83
84 402840713e0d3616013e0d3618d20013 cde19 2 0 0 ss 2013-4-15 18:20:49 abc19 16F37E59FE494AC9B17A2340387335EF 84
85 402840713e0d3616013e0d3618d20012 cde18 2 0 0 ss 2013-4-15 18:20:49 abc18 16F37E59FE494AC9B17A2340387335EF 85
86 402840713e0d3616013e0d3618d20011 cde17 2 0 0 ss 2013-4-15 18:20:49 abc17 16F37E59FE494AC9B17A2340387335EF 86
87 402840713e0d3616013e0d3618d20010 cde16 2 0 0 ss 2013-4-15 18:20:49 abc16 16F37E59FE494AC9B17A2340387335EF 87
88 402840713e0d3616013e0d3618d0000f cde15 2 0 0 ss 2013-4-15 18:20:49 abc15 16F37E59FE494AC9B17A2340387335EF 88
89 402840713e0d3616013e0d3618cf000e cde14 2 0 0 ss 2013-4-15 18:20:49 abc14 16F37E59FE494AC9B17A2340387335EF 89
90 402840713e0d3616013e0d3618cf000d cde13 2 0 0 ss 2013-4-15 18:20:49 abc13 16F37E59FE494AC9B17A2340387335EF 90
91 402840713e0d3616013e0d3618cf000c cde12 2 0 0 ss 2013-4-15 18:20:49 abc12 16F37E59FE494AC9B17A2340387335EF 91
92 402840713e0d3616013e0d3618cf000b cde11 2 0 0 ss 2013-4-15 18:20:49 abc11 16F37E59FE494AC9B17A2340387335EF 92
93 402840713e0d3616013e0d3618cf000a cde10 2 0 0 ss 2013-4-15 18:20:49 abc10 16F37E59FE494AC9B17A2340387335EF 93
94 402840713e0d3616013e0d3618ce0009 cde9 2 0 0 ss 2013-4-15 18:20:49 abc9 16F37E59FE494AC9B17A2340387335EF 94
95 402840713e0d3616013e0d3618ce0008 cde8 2 0 0 ss 2013-4-15 18:20:49 abc8 16F37E59FE494AC9B17A2340387335EF 95
96 402840713e0d3616013e0d3618ce0007 cde7 2 0 0 ss 2013-4-15 18:20:49 abc7 16F37E59FE494AC9B17A2340387335EF 96
97 402840713e0d3616013e0d3618ce0006 cde6 2 0 0 ss 2013-4-15 18:20:49 abc6 16F37E59FE494AC9B17A2340387335EF 97
98 402840713e0d3616013e0d3618ce0005 cde5 2 0 0 ss 2013-4-15 18:20:49 abc5 16F37E59FE494AC9B17A2340387335EF 98
99 402840713e0d3616013e0d3618ce0004 cde4 2 0 0 ss 2013-4-15 18:20:49 abc4 16F37E59FE494AC9B17A2340387335EF 99
100 402840713e0d3616013e0d3618ce0003 cde3 2 0 0 ss 2013-4-15 18:20:49 abc3 16F37E59FE494AC9B17A2340387335EF 100
101 402840713e0d3616013e0d3618ce0002 cde2 2 0 0 ss 2013-4-15 18:20:49 abc2 16F37E59FE494AC9B17A2340387335EF 101
102 402840713e0d3616013e0d3618cd0001 cde1 2 0 0 ss 2013-4-15 18:20:49 abc1 16F37E59FE494AC9B17A2340387335EF 102
103 402840713e0cb39c013e0cb41c040000 dfsdf 2 1 0 ss 2013-4-15 15:58:50 sdfsdfsdf 16F37E59FE494AC9B17A2340387335EF 103
104 402840713e0c9424013e0c94a8c30000 fffff 2 1 0 ss 2013-4-15 15:24:29 dgfdgfdg 16F37E59FE494AC9B17A2340387335EF 104
105 402840713e0c8e00013e0c8f1d190000 asf 2 2 0 ss 2013-4-15 15:18:25 dsfds 16F37E59FE494AC9B17A2340387335EF 105
106 402840713e0c80ce013e0c8158630000 ccc 2 1 0 ss 2013-4-15 15:03:23 dfsd 16F37E59FE494AC9B17A2340387335EF 106
107 402840713e0c5a97013e0c5b0c200000 hhhhh 2 2 0 ss 2013-4-15 14:21:33 fds 16F37E59FE494AC9B17A2340387335EF 107
108 402840713e0c58cd013e0c592b550000 hff 2 0 0 ss 2013-4-15 14:19:30 sdfs 16F37E59FE494AC9B17A2340387335EF 108
109 402840713e0c272d013e0c2e12c40000 gsfsdf 2 0 0 ss 2013-4-15 13:32:26 fdsfds 16F37E59FE494AC9B17A2340387335EF 109
110 402840713e0c25ef013e0c265f720000 fdsf 2 0 0 ss 2013-4-15 13:24:01 fsdfsdf 16F37E59FE494AC9B17A2340387335EF 110
111 402840713e0c2237013e0c228d4b0000 fdsf 2 0 0 ss 2013-4-15 13:19:51 sdfs 16F37E59FE494AC9B17A2340387335EF 111
112 402840713e0c1cc9013e0c1d0dc30000 fsdf 2 0 0 ss 2013-4-15 13:13:50 sdfdsfds 16F37E59FE494AC9B17A2340387335EF 112
113 402840713e0c1be8013e0c1c33bf0000 fsfds 2 0 0 ss 2013-4-15 13:12:54 fdsfsd 16F37E59FE494AC9B17A2340387335EF 113
114 402840713e0c1b0b013e0c1b72320000 fsfsd 2 0 0 ss 2013-4-15 13:12:05 fsdfsd 16F37E59FE494AC9B17A2340387335EF 114
115 402840713e0c19a9013e0c19f49b0000 GDFG 2 0 0 ss 2013-4-15 13:10:27 FDGDFGDF 16F37E59FE494AC9B17A2340387335EF 115
116 402840713e0c17c7013e0c184b200000 fdsfsd 2 0 0 ss 2013-4-15 13:08:38 fsdfsdfs 16F37E59FE494AC9B17A2340387335EF 116
117 402840713e0c0edc013e0c0f79d20000 fsdfds 2 0 0 ss 2013-4-15 12:59:00 fdsfdsfdsfd 16F37E59FE494AC9B17A2340387335EF 117
118 402840713e0bee4f013e0bef28150000 fdsfsd 2 0 0 ss 2013-4-15 12:23:42 fdsfsdf 16F37E59FE494AC9B17A2340387335EF 118
119 402840713e0b92fc013e0b9418cf0000 fdsfdsf 2 0 0 ss 2013-4-15 10:44:15 dsfdsfdsfds 16F37E59FE494AC9B17A2340387335EF 119
but when i code
select row_.*, rownum rownum_ from (select topic0_.id as id6_, topic0_.title as title6_, topic0_.publish_author_id as publish3_6_, topic0_.reply_number as reply4_6_, topic0_.read_number as read5_6_, topic0_.publish_author_name as publish6_6_, topic0_.publish_time as publish7_6_, topic0_.last_reply_time as last8_6_, topic0_.last_reply_name as last9_6_, topic0_.main_text as main10_6_, topic0_.FORUM_ID as FORUM11_6_ from topic topic0_ where topic0_.FORUM_ID='16F37E59FE494AC9B17A2340387335EF' order by case when topic0_.last_reply_time is not null then topic0_.last_reply_time else topic0_.publish_time end desc) row_ where rownum <=6;
the result is
ID6_ TITLE6_ PUBLISH3_6_ REPLY4_6_ READ5_6_ PUBLISH6_6_ PUBLISH7_6_ LAST8_6_ LAST9_6_ MAIN10_6_ FORUM11_6_ ROWNUM_
1 402840713e108ea4013e1090c95c0001 trytr 2 2 0 ss 2013-4-16 9:58:44 2013-4-16 9:59:21 ss fdsfds 16F37E59FE494AC9B17A2340387335EF 1
2 402840713e108ea4013e1090127e0000 trytr 2 0 0 ss 2013-4-16 9:57:57 ytrytr 16F37E59FE494AC9B17A2340387335EF 2
3 402840713e0d3616013e0d3618c20000 cde0 2 0 0 ss 2013-4-15 18:20:49 abc0 16F37E59FE494AC9B17A2340387335EF 3
4 402840713e0d3616013e0d3618ce0003 cde3 2 0 0 ss 2013-4-15 18:20:49 abc3 16F37E59FE494AC9B17A2340387335EF 4
5 402840713e0d3616013e0d3618ce0002 cde2 2 0 0 ss 2013-4-15 18:20:49 abc2 16F37E59FE494AC9B17A2340387335EF 5
6 402840713e0d3616013e0d3618cd0001 cde1 2 0 0 ss 2013-4-15 18:20:49 abc1 16F37E59FE494AC9B17A2340387335EF 6
why ? i want the result is:
ID6_ TITLE6_ PUBLISH3_6_ REPLY4_6_ READ5_6_ PUBLISH6_6_ PUBLISH7_6_ LAST8_6_ LAST9_6_ MAIN10_6_ FORUM11_6_ ROWNUM_
1 402840713e108ea4013e1090c95c0001 trytr 2 2 0 ss 2013-4-16 9:58:44 2013-4-16 9:59:21 ss fdsfds 16F37E59FE494AC9B17A2340387335EF 1
2 402840713e108ea4013e1090127e0000 trytr 2 0 0 ss 2013-4-16 9:57:57 ytrytr 16F37E59FE494AC9B17A2340387335EF 2
3 402840713e0d3616013e0d3618c20000 cde0 2 0 0 ss 2013-4-15 18:20:49 abc0 16F37E59FE494AC9B17A2340387335EF 3
4 402840713e0d3616013e0d3618da0063 cde99 2 0 0 ss 2013-4-15 18:20:49 abc99 16F37E59FE494AC9B17A2340387335EF 4
5 402840713e0d3616013e0d3618da0062 cde98 2 0 0 ss 2013-4-15 18:20:49 abc98 16F37E59FE494AC9B17A2340387335EF 5
6 402840713e0d3616013e0d3618da0061 cde97 2 0 0 ss 2013-4-15 18:20:49 abc97 16F37E59FE494AC9B17A2340387335EF 6
If somebody could explain to me I'd be most grateful.
In your first query you have:
select row_.*, rownum rownum_ from (select ... order by ...) row_;
In the second you have:
select row_.*, rownum rownum_ from (select ... order by ...) row_
where rownum <= 6;
rownum is a pseudo-column that is generated as the last step in the query, over the result set that has already been produced. The where rownum <= 6 is being applied against the results of the inner select, but at that point in the outer select you have no explicit order by clause so the order the rownum will be assigned isn't guaranteed.
You could either repeat the order by clause in the outer query, which would make it even more unpleasant to deal with, or more simply move the rownum inside the inner select:
select * from (select ..., rownum rownum_ from ... order by ...)
where rownum_ <= 6;
It's unclear if the results form the your first query are in the correct order anyway; probably, but again probably not guaranteed, so you can also order that to make sure:
select * from (select ..., rownum rownum_ from ... order by ...)
where rownum_ <= 6
order by rownum_;
(Another approach would be to use an analytic function like dense_rank() against the case you're using in the order by, and then use that in the outer where to restrict the number of rows, but that's getting a bit away from the question...)