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

User · Answer

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.

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sql-server] SQL Error with Order By in Subquery

Examples related to sql-server

Examples related to sql-order-by