How to keep the order of a subquery

[Baron]

Is it possible to make a subquery and keep the same order without the need to explitly ordering it outside the subquery?

For Example, I have this block and want to have an alphabetical ordered products:

create or replace table mytable (idnr int, productname varchar(40), price double);

insert into mytable values
(1,'Apple', 10),
(2,'Banana', 20),
(3,'Ananas', 30),
(4,'Orange', 40),
(5,'Plum', 50);

select * from (select * from mytable order by productname)T

Of course it works if I expility order it outside like this:

select * from (select * from mytable order by productname)T order by productname

But the problem is that the subquery is generated from another query so that I dont know the column names outside the subquery.

[justin_willey]

I appreciate you don't know all the column names, but do you know the ones you want to sort by?

[Baron]

I know neither the column names nor the numbers (ordinal positions) of the columns.

The inner query is acutally the result set of a procedure, as per the reply of Volker, the procedure will be extended to deliver the ordinal positions as meta data, so that I can reformulate my outer complete query based on this info.

[VolkerBarth · Accepted solution]

AFAIK, there's no general way to keep a sorted subquery "sorted" within the FROM clause. It is not different from using a base table or view in a FROM clause whose rows are also generally unordered. To sort the whole query in a similar fashion, you need to know the names or ordinal positions of the column the subquery is ordered by. (And to make it worse: In case the subquery is ordered by values that are not part of the subquery's result set, you possibly cannot "re-construct" that ordering at all... imagine the subquery would just be "select id, price from mytable order by productname".)

Of course you can number the rows within a subquery by adding a column with a row_number() function using the same sort order, and lateron use that added column's name to order by whole query:

select *
from
   (select *, row_number() over (order by productname) myNumber
    from mytable order by productname
   ) T
order by myNumber;

...but here you would also have to know the name of the column(s) the subquery is ordered by, so that's probably no improvement. (I do assume your real subquery is the result set of a view with a TOP clause and an ORDER BY or a stored procedure, so without having to specify the ORDER BY within the subquery, as otherwise you probably have to know the subquery's schema anyway to use it within the whole query...)

---

If this is within a stored procedure/function, you could try to materialize the subquery's result into a local temporary table with an additional DEFAULT AUTOINCREMENT RowNumber field and use that to keep the ordering. Here's a (possibly not optimal) attempt via CREATE TABLE AS/LIKE... and an INSERT WITH AUTO NAME:

-- create a temporary table based on the subquery's schema (and contents)
create local temporary table MyTempTable1 as (select * from mytable) not transactional;
-- create another temporary table based on the first's schema and an additonal DEFAULT INCREMENT column
create local temporary table MyTempTable2 (like MyTempTable1, MyRowNumber int default autoincrement) not transactional;
-- first temp table is not needed anymore
drop table MyTempTable1;
-- materialize the subquery's result set and keep the ordering via the MyRowNumber column
insert MyTempTable2
with auto name
select * from mytable order by productname;
-- subquery's result set is materialized including the sort order
select * from MyTempTable2 order by MyRowNumber;

[Baron · Accepted solution]

Thanks for the reply, All your suggestions (temporary table, row_number) could help but unfortunately at the cost of performance (with which I already sever problems have).

As mentioned the problem is that the ordinal positions of the columns are not fix.

I think I should find my own tricks (based on my own business logic).

[VolkerBarth · Accepted solution]

It would be helpful to know why you do not know the name and/or ordinal position of the subquery's result set - do you call a stored procedure with variable result sets? - If so, could that procedure not also tell about its ordering as some kind of meta information?

Well, if you need to know the result set's structure, you may use the sa_describe_query() system procedure (possibly with add_keys = 1) and analyse its result set - this will tell the number, names, positions and (detailed) data types of the columns and whether they are part of a key... - but of course such a call will also influence performance (and might not tell the sort order:/).

[Baron · Accepted solution]

Thanks for the reply, yes with meta data I can convey the order information.

[chris_keating]

In the absence of an order by, the server can build a result in whatever order is efficient. Without an ORDER BY clause, executing the same query more than once could return rows with different ordering.

[chris_keating]

You can order by a column number so if you know the column number for product name in the result set, you can do ORDER BY n. In the example above, use ORDER BY 2.

[Baron]

Thanks for the reply, the problem is that I don't know alsow the order of the column after which I want to sort. It is not always the column 2 after which I need to sort.