Sort Order in SQL Proc

Question

Query -

SELECT B.TYPE,
                B.TYPE_VAL,
                B.COMP,
                B.start,
                B.end,
                B.lt,
                B.modify
           FROM table B
         WHERE B.start <= sysdate
          AND B.end >= sysdate
            AND TRIM(B.type_val) IN
                ('111-AAA')
          ORDER BY B.type_val,
                   B.type,
                   B.comp DESC;

OUTPUT

+------+----------+------+-----------+------------+----+-----------------+
| TYPE | TYPE_VAL | COMP |   START   |    END     | LT |     MODIFY      |
+------+----------+------+-----------+------------+----+-----------------+
| AS   | 111-AAA  | **   | 5/10/2016 | 12/31/9999 |  1 | 5/10/2016 11:12 |
| AS   | 111-AAA  | **   | 3/8/2016  | 12/31/9999 | 68 | 3/8/2016 0:34   |
| BS   | 111-AAA  | **   | 6/16/2015 | 6/16/2015  |  7 | 6/16/2015 10:32 |
| CS   | 111-AAA  | **   | 5/10/2016 | 12/31/9999 |  1 | 5/10/2016 11:12 |
| CS   | 111-AAA  | **   | 3/8/2016  | 12/31/9999 | 13 | 3/8/2016 0:34   |
| HS   | 111-AAA  | **   | 5/10/2016 | 12/31/9999 |  1 | 5/10/2016 11:12 |
| HS   | 111-AAA  | **   | 3/8/2016  | 12/31/9999 | 13 | 3/8/2016 0:34   |
| MS   | 111-AAA  | **   | 5/10/2016 | 12/31/9999 |  1 | 5/10/2016 11:12 |
| MS   | 111-AAA  | **   | 3/8/2016  | 12/31/9999 | 13 | 3/8/2016 0:34   |
| OS   | 111-AAA  | **   | 5/10/2016 | 12/31/9999 |  1 | 5/10/2016 11:12 |
| OS   | 111-AAA  | **   | 3/8/2016  | 12/31/9999 | 13 | 3/8/2016 0:34   |
| PS   | 111-AAA  | **   | 3/8/2016  | 12/31/9999 | 13 | 3/8/2016 0:34   |
| PS   | 111-AAA  | **   | 5/10/2016 | 12/31/9999 |  1 | 5/10/2016 11:12 |
| S    | 111-AAA  | **   | 5/10/2016 | 12/31/9999 |  1 | 5/10/2016 11:12 |
| S    | 111-AAA  | **   | 3/8/2016  | 12/31/9999 | 13 | 3/8/2016 0:34   |
| VS   | 111-AAA  | **   | 5/10/2016 | 12/31/9999 |  1 | 5/10/2016 11:12 |
| VS   | 111-AAA  | **   | 3/8/2016  | 12/31/9999 | 13 | 3/8/2016 0:34   |
+------+----------+------+-----------+------------+----+-----------------+

If you notice the data being pulled is in form of latest one first then older one.

For eg for TYPE AS-

| AS   | 111-AAA  | **   | 5/10/2016 | 12/31/9999 |  1 | 5/10/2016 11:12 |
| AS   | 111-AAA  | **   | 3/8/2016  | 12/31/9999 | 68 | 3/8/2016 0:34   |

Latest data i.e 5/10/2016 is first and then 3/8/2016. (MM/DD/YY format)

But there is one exception here, for TYPE PS - 3/8/2016 is above 5/10/2016 unlike all the others.

| PS   | 111-AAA  | **   | 3/8/2016  | 12/31/9999 | 13 | 3/8/2016 0:34   |
| PS   | 111-AAA  | **   | 5/10/2016 | 12/31/9999 |  1 | 5/10/2016 11:12 |

According to my knowledge the latest one should ideally be on the top if the sort order are same (TYPE,TYPE_VAL and COMP are same, so no ordering).

My question here is - Why this particular record is being pulled in reverse order ? Shouldn't it be like how all the other million records are pulled? There must be SOME PARTICULAR reason why is this happening.

I know most of you will reference to the fact that Oracle has no particular way of returning rows but this isn't the case here.

http://stackoverflow.com/questions/899514/default-row-ordering-for-select-query-in-oracle — Mottor, May 25 '16 at 12:02

score 2 · Answer 1 · answered May 25 '16 at 11:54

2

If you do not specify an explicit ORDER BY the database can basically return the rows in any order it sees fit. This can be insertion order, but does not have to be.

If you want to sort on the start column as well, add it in your ORDER BY:

       ORDER BY B.type_val,
               B.type,
               B.comp DESC,
               B.start;

answered May 25 '16 at 11:54

MichielB

4,181
1
30
39

I agree with the fact that it can return in any order but somehow I still do not understand why is it happening with just one record. – Pirate X May 25 '16 at 12:13
1

@PirateX It's happening because that's just how Oracle happened to fetch the data. If the rows had been in different blocks / order, then you'd have probably gotten a different ordering again. Remember that when you store rows in a heap table, there is no internal ordering. If you delete a row, Oracle may well decide to insert a new row in its place, rather than at the "end" of the table. The simple matter is, none of us know what Oracle does exactly under-the-covers, nor does it matter particularly. The fact remains: if a column is not in an order by clause, you cannot guarantee the order. – Boneist May 25 '16 at 13:29
@Boneist I think ` If you delete a row, Oracle may well decide to insert a new row in its place, rather than at the "end" of the table.` this makes sense for me. A little bit of satisfaction in my head about sorting. – Pirate X May 25 '16 at 15:58
@PirateX for more info, check out [the documentation](https://docs.oracle.com/cd/E11882_01/server.112/e40540/tablecls.htm#CNCPT1128) – Boneist May 25 '16 at 16:21

score 2 · Answer 2 · answered May 25 '16 at 11:56

2

Your order by clause is:

ORDER BY B.type_val, B.type, B.comp DESC;

Your pairs of records have exactly the same values for these three columns. Hence, they can appear in any order. It is a coincidence if some are in the "right" order, because the query provides no information as to the right ordering.

If you want them in a particular order, then you need to include that information in the ORDER BY. For instance:

ORDER BY B.type_val, B.type, B.comp DESC, B.Modify DESC

answered May 25 '16 at 11:56

Gordon Linoff

1,242,037
58
646
786

Gordon, First I'm a big fan of you. Second, all records are in 'right' order but one. I genuinely think that there must be some other factor which is affecting that record. It can't be merely a coincidence :/ – Pirate X May 25 '16 at 12:18
2

@PirateX `it can't be merely a coincidence` ... um, it can. If you toss an untampered-with coin 50 times in a row and all 50 times it came out heads, would you consider that to be not a coincidence? It's pure luck that you got the rows out in the order that you got them, because (and I think we can't repeat this often enough) **you did not include that column in the order by**. – Boneist May 25 '16 at 13:33

score 1 · Answer 3 · answered May 25 '16 at 11:56

1

Exactly, if no order is specified on column (MODIFY specifically), rows of the result set may be returned in any order on that column or in no order at all. Oracle, MS Sql, any other DB will behave the same way. Why some paticular rows have this paticular ordering on MODIFY and the other rows have different ordering? I don't believe we can know it, too much factors involved.

answered May 25 '16 at 11:56

Serg

22,285
5
21
48

Yes there are a lot of factors involved. Can you tell me what all factors are there? I don't want explanation for every factor, I'll simply check against each one by myself. – Pirate X May 25 '16 at 12:14
Physical order of reading extents of disk space for example. I really don't know how DB user or admin can control or at lest report it. – Serg May 25 '16 at 12:56

score 0 · Answer 4 · answered May 25 '16 at 12:01

0

If you not include the columns in the order query, the database can or not order others columns, but you can't assume they will be ordered.

Pay attention to the SQL provider that you use (MySQL, Orache...), every one can work different.

answered May 25 '16 at 12:01

José Miguel

11
4

Sort Order in SQL Proc

4 Answers4