Is Order in a Subquery Guaranteed to Be Preserved

Is order in a subquery guaranteed to be preserved?

No, put the order by in the outer query:

SELECT name FROM
  (SELECT name, age FROM people WHERE age >= 18) p
ORDER BY p.age DESC
LIMIT 10

The inner (sub) query returns a result-set. If you put the order by there, then the intermediate result-set passed from the inner (sub) query, to the outer query, is guaranteed to be ordered the way you designate, but without an order by in the outer query, the result-set generated by processing that inner query result-set, is not guaranteed to be sorted in any way.

Subquery in UNION select not preserving order

In your second code block, your ORDER BY clause is inside your subquery. Once you leave the subquery and move to the outer query, there is no ORDER BY clause. So there is no guarantee for order.

In your first code block, there is in fact an ORDER BY clause, so you are guaranteed an order in that case, but it is on 'priority'.

If you're looking to order by 'priority', then by the 'count' in the subquery, you'll have to find a way to get the 'count' as part of the outer query:

select    searchword = car, 
          priority = 0, 
          [count] = null 
from      Cars

union 
select    animal, 1, null
from      Animals 
where     animal <> 'dog'

union 
select    color, 2, [count]
from      colors 

order by  priority ASC, count desc

In this case, the refactoring eliminates the need for the subquery altogether.

By the way, I got rid of some unnecessary distincts (union already does distinct).

Is order by clause allowed in a subquery

Yes: It should not be done, because it does not make sense conceptually.

The subquery will be used in some outer query (otherwise it would be pointless), and that outer query will have to do ordering anyway, so there's no point ordering the subquery.

This is because query results in SQL will come in no particular order, unless you use an explicit ORDER. So even if you used ORDER in the subquery, you have no guarantee that this will affect the order of the results from the outer query; so it's pointless.

It may of course make a difference in some specific RDBMS because of its implementation, but that will be implementation-specific, and not something you should rely on.

Edit: Of course, if you use TOP or LIMIT in the subquery, you will need to use ORDER. But that's not standard SQL anyway...

Preserve order from subquery (with GROUP BY and ORDER BY)

SELECT 
latitude, 
longitude, 
count(1) as "Count", 
min(timestamp) as "Start",
max(timestamp) as "End"

FROM table 
GROUP BY latitude, longitude
ORDER BY min(timestamp) asc

Does UNION ALL guarantee the order of the result set

There is no inherent order, you have to use ORDER BY. For your example you can easily do this by adding a SortOrder to each SELECT. This will then keep the records in the order you want:

SELECT 'O', 1 SortOrder
UNION ALL
SELECT 'R', 2
UNION ALL
SELECT 'D', 3
UNION ALL
SELECT 'E', 4
UNION ALL
SELECT 'R', 5
ORDER BY SortOrder

You cannot guarantee the order unless you specifically provide an order by with the query.

Does order by in view guarantee order of select?

You can't count on the order of rows in any query that doesn't have an explicit ORDER BY clause. If you query an ordered view, but you don't include an ORDER BY clause, be pleasantly surprised if they're in the right order, and don't expect it to happen again.

That's because the query optimizer is free to access rows in different ways depending on the query, table statistics, row counts, indexes, and so on. If it knows your query doesn't have an ORDER BY clause, it's free to ignore row order in order (cough) to return rows more quickly.

Slightly off-topic . . .

Sort order isn't necessarily identical across platforms even for well-known collations. I understand that sorting UTF-8 on Mac OS X is particularly odd. (PostgreSQL developers call it broken.) PostgreSQL relies on strcoll(), which I understand relies on the OS locales.

It's not clear to me how PostgreSQL 9.1 will handle this. In 9.1, you can have multiple indexes, each with a different collation. An ORDER BY that doesn't specify a collation will usually use the collation of the underlying base table's columns, but what will the optimizer do with an index that specifies a different collation than an unindexed column in the base table?

ORDER BY subquery and ROWNUM goes against relational philosophy?

The ORDER BY in this context is in effect Oracle's proprietary syntax for generating an "ordered" row number on a (logically) unordered set of rows. This is a poorly designed feature in my opinion but the equivalent ISO standard SQL ROW_NUMBER() function (also valid in Oracle) may make it clearer what is happening:

select *
from (
  select ROW_NUMBER() OVER (ORDER BY price) rn, *
  from table
) t
where rn <= 7;

In this example the ORDER BY goes where it more logically belongs: as part of the specification of a derived row number attribute. This is more powerful than Oracle's version because you can specify several different orderings defining different row numbers in the same result. The actual ordering of rows returned by this query is undefined. I believe that's also true in your Oracle-specific version of the query because no guarantee of ordering is made when you use ORDER BY in that way.

It's worth remembering that Oracle is not a Relational DBMS. In common with other SQL DBMSs Oracle departs from the relational model in some fundamental ways. Features like implicit ordering and DISTINCT exist in the product precisely because of the non-relational nature of the SQL model of data and the consequent need to work around keyless tables with duplicate rows.

SQL Error with Order By in Subquery

This is the error you get (emphasis mine):

The ORDER BY clause is invalid in
views, inline functions, derived
tables, subqueries, and common table
expressions, unless TOP or FOR XML is
also specified.

So, how can you avoid the error? By specifying TOP, would be one possibility, I guess.

SELECT (
  SELECT TOP 100 PERCENT
  COUNT(1) FROM Seanslar WHERE MONTH(tarihi) = 4
  GROUP BY refKlinik_id
  ORDER BY refKlinik_id
) as dorduncuay

---

Related Topics