CODEWITHSUNDEEP
sqlsubquerysql-order-by
Prady
Prady

Reputation: 11300

Is order by clause allowed in a subquery

Is there any reason why or why not you should do an 'order by' in a subquery?

Upvotes: 11

Views: 51008

Answers (9)

Marko
Marko

Reputation: 1924

Smarter people say that is not proper/valid way to do it. In my case SELECT TOP 100 PERCENT in sub-query solved the problem.

Cheers

Upvotes: 2

Jordan Parmer
Jordan Parmer

Reputation: 37164

You should not use it. According to the 'Art of SQL', this actually prevents the optimizer from performing various optimizations that it could otherwise do because this pre-maturely transforms the data.

Upvotes: 1

AdaTheDev
AdaTheDev

Reputation: 147224

No ORDER BY is valid in a subquery when you are interested in a subset of the overall data, hence you always need a TOP (SQL Server). There's no point having an ORDER BY without TOP in a subquery because the overall ordering of the results is handled by the outer query.

Upvotes: 1

SQLMenace
SQLMenace

Reputation: 134933

Unless you use top it is not useful since you will be ordering in the outer query anyway

Upvotes: 3

loginx
loginx

Reputation: 1112

It's totally legit. I.e. SELECT id FROM entries WHERE author_id IN (SELECT id FROM authors ORDER BY name DESC) but you'll really get the same results usually.

Upvotes: -4

sleske
sleske

Reputation: 83577

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...

Upvotes: 14

Justin Niessner
Justin Niessner

Reputation: 245399

Depending on the size of the sub-query, it will impact performance to a varrying degree.

Order shouldn't matter on a sub-query though. You should be able to move the Order By portion to the Outer Query (which should be the one returning the final results).

Upvotes: 1

gbn
gbn

Reputation: 432190

You can do it, but I wouldn't usually unless you have a need.

The optimiser will ignore it (or throw an error?)

See "Intermediate materialisation" for some usages.

Upvotes: 1

Quassnoi
Quassnoi

Reputation: 425261

You should use it if the subquery uses some kind of LIMIT / TOP.

SQL Server will not allow it unless the subquery contains TOP or FOR XML clause as well:

-- Fails
WITH    q(id) AS
        (
        SELECT  1
        UNION ALL
        SELECT  2
        )
SELECT  *
FROM    (
        SELECT  *
        FROM    q
        ORDER BY
                id DESC
        ) q2

-- Succeeds
WITH    q(id) AS
        (
        SELECT  1
        UNION ALL
        SELECT  2
        )
SELECT  *
FROM    (
        SELECT  TOP 1 *
        FROM    q
        ORDER BY
                id DESC
        ) q2

-- Succeeds, but ORDER BY is ignored
WITH    q(id) AS
        (
        SELECT  1
        UNION ALL
        SELECT  2
        )
SELECT  *
FROM    (
        SELECT  TOP 100 PERCENT *
        FROM    q
        ORDER BY
                id DESC
        ) q2

Upvotes: 12

Related Questions