SQL Error with Order By in Subquery

Question

I'm working with SQL Server 2005.

My query is:

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

And the error:

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.

How can I use ORDER BY in a sub query?

Answer 1

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

Answer 2

If you're working with SQL Server 2012 or later, this is now easy to fix. Add an offset 0 rows:

SELECT (
  SELECT
  COUNT(1) FROM Seanslar WHERE MONTH(tarihi) = 4
  GROUP BY refKlinik_id
  ORDER BY refKlinik_id OFFSET 0 ROWS
) as dorduncuay

Answer 3

Besides the fact that order by doesn't seem to make sense in your query.... To use order by in a sub select you will need to use TOP 2147483647.

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

My understanding is that "TOP 100 PERCENT" doesn't gurantee ordering anymore starting with SQL 2005:

In SQL Server 2005, the ORDER BY clause in a view definition is used only to determine the rows that are returned by the TOP clause. The ORDER BY clause does not guarantee ordered results when the view is queried, unless ORDER BY is also specified in the query itself.

See SQL Server 2005 breaking changes

Hope this helps, Patrick

Answer 4

If building a temp table, move the ORDER BY clause from inside the temp table code block to the outside.

Not allowed:

SELECT * FROM (
SELECT A FROM Y
ORDER BY Y.A
) X;

Allowed:

SELECT * FROM (
SELECT A FROM Y
) X
ORDER BY X.A;

Answer 5

You don't need order by in your sub query. Move it out into the main query, and include the column you want to order by in the subquery.

however, your query is just returning a count, so I don't see the point of the order by.

Answer 6

maybe this trick will help somebody

SELECT
    [id],
    [code],
    [created_at]                          
FROM
    ( SELECT
        [id],
        [code],
        [created_at],
        (ROW_NUMBER() OVER (
    ORDER BY
        created_at DESC)) AS Row                                 
    FROM
        [Code_tbl]                                 
    WHERE
        [created_at] BETWEEN '2009-11-17 00:00:01' AND '2010-11-17 23:59:59'                                  
        )  Rows                          
WHERE
    Row BETWEEN 10 AND    20;

here inner subquery ordered by field created_at (could be any from your table)

Answer 7

A subquery (nested view) as you have it returns a dataset that you can then order in your calling query. Ordering the subquery itself will make no (reliable) difference to the order of the results in your calling query.

As for your SQL itself: a) I seen no reason for an order by as you are returning a single value. b) I see no reason for the sub query anyway as you are only returning a single value.

I'm guessing there is a lot more information here that you might want to tell us in order to fix the problem you have.

Answer 8

Add the Top command to your sub query...

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

:)

Answer 9

Try moving the order by clause outside sub select and add the order by field in sub select

SELECT * FROM 

(SELECT COUNT(1) ,refKlinik_id FROM Seanslar WHERE MONTH(tarihi) = 4 GROUP BY refKlinik_id)
as dorduncuay 

ORDER BY refKlinik_id 

Answer 10

In this example ordering adds no information - the COUNT of a set is the same whatever order it is in!

If you were selecting something that did depend on order, you would need to do one of the things the error message tells you - use TOP or FOR XML

Answer 11

For me this solution works fine as well:

SELECT tbl.a, tbl.b
FROM (SELECT TOP (select count(1) FROM yourtable) a,b FROM yourtable order by a) tbl

Answer 12

Good day

for some guys the order by in the sub-query is questionable. the order by in sub-query is a must to use if you need to delete some records based on some sorting. like

delete from someTable Where ID in (select top(1) from sometable where condition order by insertionstamp desc)

so that you can delete the last insertion form table. there are three way to do this deletion actually.

however, the order by in the sub-query can be used in many cases.

for the deletion methods that uses order by in sub-query review below link

http://web.archive.org/web/20100212155407/http://blogs.msdn.com/sqlcat/archive/2009/05/21/fast-ordered-delete.aspx

i hope it helps. thanks you all

Answer 13

For a simple count like the OP is showing, the Order by isn't strictly needed. If they are using the result of the subquery, it may be. I am working on a similiar issue and got the same error in the following query:

-- I want the rows from the cost table with an updateddate equal to the max updateddate:

    SELECT * FROM #Costs Cost
    INNER JOIN
    (
        SELECT Entityname, costtype, MAX(updatedtime) MaxUpdatedTime
        FROM #HoldCosts cost
        GROUP BY Entityname, costtype
        ORDER BY Entityname, costtype  -- *** This causes an error***
    ) CostsMax
        ON  Costs.Entityname = CostsMax.entityname
        AND Costs.Costtype = CostsMax.Costtype
        AND Costs.UpdatedTime = CostsMax.MaxUpdatedtime
    ORDER BY Costs.Entityname, Costs.costtype

-- *** To accomplish this, there are a few options:

-- Add an extraneous TOP clause, This seems like a bit of a hack:

    SELECT * FROM #Costs Cost
    INNER JOIN
    (
        SELECT TOP 99.999999 PERCENT Entityname, costtype, MAX(updatedtime) MaxUpdatedTime
        FROM #HoldCosts cost
        GROUP BY Entityname, costtype
        ORDER BY Entityname, costtype  
    ) CostsMax
        ON Costs.Entityname = CostsMax.entityname
        AND Costs.Costtype = CostsMax.Costtype
        AND Costs.UpdatedTime = CostsMax.MaxUpdatedtime
    ORDER BY Costs.Entityname, Costs.costtype

-- **** Create a temp table to order the maxCost

    SELECT Entityname, costtype, MAX(updatedtime) MaxUpdatedTime
    INTO #MaxCost
    FROM #HoldCosts cost
    GROUP BY Entityname, costtype
    ORDER BY Entityname, costtype  

    SELECT * FROM #Costs Cost
    INNER JOIN #MaxCost CostsMax
        ON Costs.Entityname = CostsMax.entityname
        AND Costs.Costtype = CostsMax.Costtype
        AND Costs.UpdatedTime = CostsMax.MaxUpdatedtime
    ORDER BY Costs.Entityname, costs.costtype

Other possible workarounds could be CTE's or table variables. But each situation requires you to determine what works best for you. I tend to look first towards a temp table. To me, it is clear and straightforward. YMMV.

Answer 14

On possible needs to order a subquery is when you have a UNION :

You generate a call book of all teachers and students.

SELECT name, phone FROM teachers
UNION
SELECT name, phone FROM students

You want to display it with all teachers first, followed by all students, both ordered by. So you cant apply a global order by.

One solution is to include a key to force a first order by, and then order the names :

SELECT name, phone, 1 AS orderkey FROM teachers
UNION
SELECT name, phone, 2 AS orderkey FROM students
ORDER BY orderkey, name

I think its way more clear than fake offsetting subquery result.

Answer 15

I Use This Code To Get Top Second Salary

I am Also Get Error Like

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.

TOP 100 I Used To Avoid The Error

select * from ( select tbl.Coloumn1 ,CONVERT(varchar, ROW_NUMBER() OVER (ORDER BY (SELECT 1))) AS Rowno from ( select top 100 * from Table1 order by Coloumn1 desc) as tbl) as tbl where tbl.Rowno=2