Is Order by Clause Allowed in a Subquery

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

SQL order by not allowed in subquery

There are at least 3 issues with the query you tried:

• If you want to sort descending by number of plays, do so; don't sort by username...

• Simply put the order by outside the subquery clause: give count(*) an alias via count(*) as n_plays and then sort the outer query order by n_plays desc. Aliases declared in the select list are available in the order by clause, so no subquery (or CTE or whatever) is necessary.

• If you want the top 30 players, then that's not the top 30 percent, is it? so just isolate the top 30 *, no percent.

---

So, let's put it all together:

select top 30
  username,
  count(*) as n_plays
from plays
group by username
order by n_plays desc;

where the data was created by:

create table plays (
  id int identity not null primary key,
  username varchar(100)
  -- other fields like date/time, comment, etc.
);

declare @i int = 1;

while @i <= 1000
begin
  insert into plays (username)
  values ( 'Player' + cast( cast(rand() * 99 as int) as varchar) );
  
  set @i += 1;
end;

---

working fiddle: http://sqlfiddle.com/#!18/aebdf/4

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

Can we use order by in subquery? If not why sometime could use top(n) order by?

1. Yes, you are right we cannot use order by in a inner query. Because it is acting as a table. A table in itself needs to be sorted when queried for different purposes.

2. In your query itself the inner query is select some records using Top 2. Eventhough these are top 2 records only, they form a table with 2 records which is enough for it to recognized as a table and join it with another table

The right query will be:-

SELECT * FROM
(
    SELECT c.CustomerId, c.OrderId, DENSE_RANK() OVER(ORDER BY b.count DESC) AS RANK

    FROM CustomerOrder c

    INNER JOIN

    (SELECT CustomerId, COUNT(distinct OrderId) as Count
    FROM CustomerOrder   GROUP BY CustomerId) b 

    ON c.CustomerId = b.CustomerId
) a

WHERE RANK IN (1,2);

Hope I have answered your question.

Order by in a sub query

Try this:

Make your 2 queries.."derived" Tables. Here is a working Northwind example:

  Use Northwind
     GO

Select  OrderID , CustomerID , EmployeeID from 
( Select TOP 1 OrderID , CustomerID , EmployeeID from  dbo.Orders where ShipCountry='France' Order by ShippedDate )
as derived1
UNION ALL
Select  OrderID , CustomerID , EmployeeID from 
( Select TOP 1 OrderID , CustomerID , EmployeeID from  dbo.Orders where ShipCountry='Germany' Order by ShippedDate  )
as derived2

Here it is with your queries plugged in , but I cannot test them since I don't have your DDL.

    Select  * from 
    ( select top 1  stu_first, stu_last, sum(KIND_hours) as total from STUDENT inner join KIND
on student.stu_id=KIND.stu_id
where (12-(STU_CLASS_OF-2014))=9
group by stu_first, stu_last
order by total desc )
    as derived1
    UNION ALL
    Select  * from 
    ( select top 1  stu_first, stu_last, sum(KIND_hours) as total from STUDENT inner join KIND
on student.stu_id=KIND.stu_id
where (12-(STU_CLASS_OF-2014))=10
group by stu_first, stu_last
order by total desc  )
    as derived2

OLD ANSWER FOR POSSIBLE WORK AROUND

If you need "Order by" distinction between the two sets of data, you can use this trick:

IF OBJECT_ID('tempdb..#TableOne') IS NOT NULL
begin
        drop table #TableOne
end

IF OBJECT_ID('tempdb..#TableTwo') IS NOT NULL
begin
        drop table #TableTwo
end

CREATE TABLE #TableOne
( 
SurrogateKeyIDENTITY int not null IDENTITY (1,1) , 
NameOfOne varchar(12)
)

CREATE TABLE #TableTwo
( 
SurrogateKeyIDENTITY int not null IDENTITY (1,1) , 
NameOfTwo varchar(12)
)

Insert into #TableOne (NameOfOne)
Select 'C' as Alpha UNION ALL Select 'B' as Alpha UNION ALL Select 'D' as Alpha UNION ALL Select 'Z' as Alpha

Insert into #TableTwo (NameOfTwo)
Select 'T' as Alpha UNION ALL Select 'W' as Alpha UNION ALL Select 'X' as Alpha UNION ALL Select 'A' as Alpha

select 1 , NameOfOne from #TableOne
UNION
select 2 , NameOfTwo from #TableTwo
Order by 1 , 2 /* These are the "Ordinal Positions of the Column*/

IF OBJECT_ID('tempdb..#TableOne') IS NOT NULL
begin
        drop table #TableOne
end

IF OBJECT_ID('tempdb..#TableTwo') IS NOT NULL
begin
        drop table #TableTwo
end

SQL ORDER BY Subquery

I would expect a query that finds the maximum score for each game using group, and then joining in the rest of the information:

SELECT pp.Playername , gr.P1Score as HighestScore, gr.TimeElapsed 
FROM gamerecord gr inner join
     playerprofile pp
     ON pp.PID = gr.PID inner join
     (SELECT PID, max(p1score) as maxscore
      FROM gamerecord
      GROUP BY PID
     ) grmax
     ON gr.PID = grmax.PID and gr.p1score = grmax.maxscore;

order by is not appropriate for this type of query.

Order by clause inside sub query causing error

As previously stated, order by is not allowing in a subquery.

The reason is that a subquery by definition is called by another query. It is that outer query that determines the order of results. Orderying an intermediate result set has no effect on the order of the final result. Thus sql server dissallows you from adding an order by to a subquery to prevent you even thinking that it has an effect in your query. If it allowed the order by, Microsoft would get a ton of bug reports: order by is not working, when it is working perfectly, it is just not applied to the final result set.

Edit in response to question about if the order by could improve join performance.

SQL Server will determine the best way to join two result sets. In your case, especially if the id is a clustered key, it will likely select the subquery in id order and then run a join based on that order (merge join). But it doesn't have to. If your where clause restricts the rows returned enough, it could decide it would be faster do something else like a nested loop join. You are trying to help the optimizer with the order by. Likely it will do the order by internally and you can check the execution plan to see. However, it has the option to do a different join it thinks that will be quicker.

Let sql server optimize itself. Sql is language that describes what you want, not how to get it. I want these columns from these tables in this order. Let sql server decide if an intermediate order by would allow it to run quicker. Don't try to force it to run a certain way. It knows far more about its capabilities and your data than either of us.

Why this LAG() Window function returns The Order by clause is invalid

Try the following modified query and see if this works for you?

with Tr as (
    select 
        d.Warehouse, t.Code, d.zDate, 
        t.ID, t.QtyIn, t.QtyOut, t.BalanceAfter
    from DocDtls d
    join Transactions t on d.DocNum = t.DocNum
)
select ID, Code, QtyIn, QtyOut, BalanceAfter,
  Lag(BalanceAfter,1,0) over (partition by Warehouse, Code order by Id) Prev_BlncAfter 
from Tr;

Order by inside a subquery

You can't. Only the outermost ORDER BY matters.

So SELECT * FROM MyView ORDER By Whatever is the only way.

Any intermediate ordering (whether ORDER BY or coincidental, or part of the lan) is ignored

---

Related Topics