Oracle: How to Implement a "Natural" Order-By in a SQL Query

Oracle: How can I implement a natural order-by in a SQL query?

You can use functions in your order-by clause. In this case,
you can split the non-numeric and numeric portions of the
field and use them as two of the ordering criteria.

select * from t
 order by to_number(regexp_substr(a,'^[0-9]+')),
          to_number(regexp_substr(a,'[0-9]+$')),
          a;

You can also create a function-based index to support this:

create index t_ix1
    on t (to_number(regexp_substr(a, '^[0-9]+')),
          to_number(regexp_substr(a, '[0-9]+$')), 
          a);

How can I get a natural numeric sort order in Oracle?

Would this help?

SQL> with test (col) as
  2  (select 'S001'   from dual union all
  3   select 'S1001'  from dual union all
  4   select 'S090'   from dual union all
  5   select 'SV911'  from dual union all
  6   select 'SV800'  from dual union all
  7   select 'Sfoofo' from dual union all
  8   select 'Szap'   from dual union all
  9   select 'Sbart'  from dual
 10  )
 11  select col
 12  from test
 13  order by substr(col, 1, 1),
 14    case when regexp_like(col, '^[[:alpha:]]\d') then to_number(regexp_substr(col, '\d+$')) end,
 15    substr(col, 2);

COL
------
S001
S090
S1001
Sbart
Sfoofo
SV800
SV911
Szap

8 rows selected.

SQL>

SQL - retain ordering based on the query params

Insert the values into a temporary table and join your select to that.

You can then do a natural order on your temporary table column.

CREATE GLOBAL TEMPORARY TABLE sort_table (
  value       VARCHAR2(100),
  sort_order  NUMBER
) ON COMMIT DELETE ROWS;

INSERT INTO sort_table VALUES ('B123',1);
INSERT INTO sort_table VALUES ('B483',2);
... etc. ...

select * from mytable
inner join sort_table
on mytable.mycolumn = sort_table.value
order by sort_table.sort_order;

To clear the temporary table, just COMMIT.

Default row ordering for select query in oracle

According to Tom Kyte: "Unless and until you add "order by" to a query, you cannot say ANYTHING about the order of the rows returned. Well, short of 'you cannot rely on the order of the rows being returned'."

See this question at asktom.com.

As for ROWNUM, it doesn't physically exist, so it can't be "freed". ROWNUM is assigned after a record is retrieved from a table, which is why "WHERE ROWNUM = 5" will always fail to select any records.

@ammoQ: you might want to read this AskTom article on GROUP BY ordering. In short:

Does a Group By clause in an Query gaurantee that the output data will be
sorted on the Group By columns in
order, even if there is NO Order By
clause?

and we said...

ABSOLUTELY NOT,

It never has, it never did, it never
will.

Sort String column which has numbers and Alphabets( Oracle SQL)

If your first order by clause ensures that the primary sort order is based on the numerical component of the UoA field, then your second order clause could actually be just the UoA field itself. I.e.

    order by 
      regexp_substr(UoA, '^\D*'), UoA;

Oracle DB version column sorting

 SELECT * FROM YOUR_TABLE
 ORDER BY 
 to_number(regexp_substr(COL1, '[^.]+', 1, 1)) DESC NULLS FIRST,
 to_number(regexp_substr(COL1, '[^.]+', 1, 2)) DESC NULLS FIRST ,
 to_number(regexp_substr(COL1, '[^.]+', 1, 3)) DESC NULLS FIRST ,
 to_number(regexp_substr(COL1, '[^.]+', 1, 4)) DESC NULLS FIRST ;