locked
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions RRS feed

  • Question

  • i want order by CID 

    but it is giving error 

    Msg 1033, Level 15, State 1, Line 9
    The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

    select f.Category,f.Name,(f.Bigbale_QTY-f.Dispatch_QTY) as [Balance],(f.Bigbale_weight-f.Dispatch_Weight) as [W_Balance] from(
    select e.Category,e.Name,isnull(min(e.[Bigbale_QTY]),0) as [Bigbale_QTY],isnull(min(e.[Bigbale_weight]),0) as [Bigbale_weight],isnull(sum(c.QTY),0) as [Dispatch_QTY],isnull(sum(c.Bweight),0) as [Dispatch_Weight] from (
    select ca.CName as Category,a.Descriptionitem as Name,min(a.CodeItem) as CodeItem,isnull(sum(b.Bpqty),0) as [Bigbale_QTY],isnull(sum(b.Bweight),0) as [Bigbale_Weight]
    from ItemMasterFile a
    inner join Catagory ca on ca.CID=a.CID
    left join Bigbalprd b on a.CodeItem=b.CodeItem
     where a.Packsize ='bigbale' and b.delID is null  and (b.trans is null or b.Trans='b') 
      group by a.Descriptionitem, ca.CName
      ORDER BY CA.CID ASC)e
    left join Dispatch_BD c on e.CodeItem=c.CodeItem 
     where c.Delidd is null  
    group by e.Name,e.Category
    
    )f


    akhter

    Thursday, July 9, 2020 2:50 AM

Answers

  • select f.Category,f.Name,(f.Bigbale_QTY-f.Dispatch_QTY) as [Balance],(f.Bigbale_weight-f.Dispatch_Weight) as [W_Balance] from (
    select e.Category,e.Name,
    
    isnull(min(e.[Bigbale_QTY]),0) as [Bigbale_QTY],isnull(min(e.[Bigbale_weight]),0) as [Bigbale_weight],isnull(sum(c.QTY),0) as [Dispatch_QTY],isnull(sum(c.Bweight),0) as [Dispatch_Weight] from (
    select ca.CName as Category, ca.CID,
    
    a.Descriptionitem as Name,min(a.CodeItem) as CodeItem,isnull(sum(b.Bpqty),0) as [Bigbale_QTY],isnull(sum(b.Bweight),0) as [Bigbale_Weight]
    from ItemMasterFile a
    inner join Catagory ca on ca.CID=a.CID
    left join Bigbalprd b on a.CodeItem=b.CodeItem
     where a.Packsize ='bigbale' and b.delID is null  and (b.trans is null or b.Trans='b') 
      group by a.Descriptionitem, ca.CName, CA.CID) e
    left join Dispatch_BD c on e.CodeItem=c.CodeItem 
     where c.Delidd is null  
    group by e.Name,e.Category
    
    )f ORDER BY f.CID
    In other words, that column needs to be part of the inner subquery return list of columns.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Marked as answer by Akhterhussain Thursday, July 9, 2020 6:12 PM
    Thursday, July 9, 2020 6:09 PM

All replies

  • Just remove that ORDER BY from your inner subquery, e.g.

    select f.Category,f.Name,(f.Bigbale_QTY-f.Dispatch_QTY) as [Balance],(f.Bigbale_weight-f.Dispatch_Weight) as [W_Balance] from (
    select e.Category,e.Name,isnull(min(e.[Bigbale_QTY]),0) as [Bigbale_QTY],isnull(min(e.[Bigbale_weight]),0) as [Bigbale_weight],isnull(sum(c.QTY),0) as [Dispatch_QTY],isnull(sum(c.Bweight),0) as [Dispatch_Weight] from (
    select ca.CName as Category,a.Descriptionitem as Name,min(a.CodeItem) as CodeItem,isnull(sum(b.Bpqty),0) as [Bigbale_QTY],isnull(sum(b.Bweight),0) as [Bigbale_Weight]
    from ItemMasterFile a
    inner join Catagory ca on ca.CID=a.CID
    left join Bigbalprd b on a.CodeItem=b.CodeItem
     where a.Packsize ='bigbale' and b.delID is null  and (b.trans is null or b.Trans='b') 
      group by a.Descriptionitem, ca.CName) e
    left join Dispatch_BD c on e.CodeItem=c.CodeItem 
     where c.Delidd is null  
    group by e.Name,e.Category
    
    )f


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, July 9, 2020 2:56 AM
  • But data still is not coming order wise


    akhter

    Thursday, July 9, 2020 3:10 AM
  • Try to keep the formation of :

    Select  From

    (Select 

      From (your joins)

      Where ..

      Group By ..

    ) f

    Order By ..


    Regards, David .

    Thursday, July 9, 2020 5:08 AM
  • Hi Akhter,

    You could not use 'order by' inside the subquery by iteslef.

    BTW, you could use it with TOP. 

    For example, select * from (select top 100 percent from ttt order by id) x. 

    In your situation, you could have two options like below. 

    1. Remove the 'order by' from your inner subquery and add it at the end instead.

    2. Add 'TOP ' in your inner subquery.

    After all, the second option may be more suitable for you.

    select f.Category,f.Name,(f.Bigbale_QTY-f.Dispatch_QTY) as [Balance],(f.Bigbale_weight-f.Dispatch_Weight) as [W_Balance] from (
    select e.Category,e.Name,isnull(min(e.[Bigbale_QTY]),0) as [Bigbale_QTY],isnull(min(e.[Bigbale_weight]),0) as [Bigbale_weight],isnull(sum(c.QTY),0) as [Dispatch_QTY],isnull(sum(c.Bweight),0) as [Dispatch_Weight] from (
    select ca.CName as Category,a.Descriptionitem as Name,min(a.CodeItem) as CodeItem,isnull(sum(b.Bpqty),0) as [Bigbale_QTY],isnull(sum(b.Bweight),0) as [Bigbale_Weight]
    from ItemMasterFile a
    inner join Catagory ca on ca.CID=a.CID
    left join Bigbalprd b on a.CodeItem=b.CodeItem
     where a.Packsize ='bigbale' and b.delID is null  and (b.trans is null or b.Trans='b') 
      group by a.Descriptionitem, ca.CName) e
    left join Dispatch_BD c on e.CodeItem=c.CodeItem 
     where c.Delidd is null  
    group by e.Name,e.Category
    
    )f
     ORDER BY f.Category,f.Name
    select f.Category,f.Name,(f.Bigbale_QTY-f.Dispatch_QTY) as [Balance],(f.Bigbale_weight-f.Dispatch_Weight) as [W_Balance] from(
    select e.Category,e.Name,isnull(min(e.[Bigbale_QTY]),0) as [Bigbale_QTY],isnull(min(e.[Bigbale_weight]),0) as [Bigbale_weight],
    isnull(sum(c.QTY),0) as [Dispatch_QTY],isnull(sum(c.Bweight),0) as [Dispatch_Weight] from (
    select top 1000 ca.CName as Category,a.Descriptionitem as Name,min(a.CodeItem) as CodeItem,isnull(sum(b.Bpqty),0) as [Bigbale_QTY],
    isnull(sum(b.Bweight),0) as [Bigbale_Weight]
    from ItemMasterFile a
    inner join Catagory ca on ca.CID=a.CID
    left join Bigbalprd b on a.CodeItem=b.CodeItem
     where a.Packsize ='bigbale' and b.delID is null  and (b.trans is null or b.Trans='b') 
      group by a.Descriptionitem, ca.CName
      ORDER BY CA.CID ASC)e
    left join Dispatch_BD c on e.CodeItem=c.CodeItem 
     where c.Delidd is null  
    group by e.Name,e.Category
    
    )f

    Best regards,

    Melissa

    -------------------------------------------

    MSDN Community Support

    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com







    Thursday, July 9, 2020 5:49 AM
  • You need to specify the desired order at the end of the process. Do you need to sort based on the column which is not part of OUTER select ?

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Proposed as answer by Tom Phillips Thursday, July 9, 2020 5:04 PM
    Thursday, July 9, 2020 1:03 PM
  • Do you need to sort based on the column which is not part of OUTER select ?

    Yes


    akhter

    Thursday, July 9, 2020 5:11 PM
  • I think you still can use it in OUTER syntax.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, July 9, 2020 5:45 PM
  • How it will use ?

    akhter

    Thursday, July 9, 2020 5:59 PM
  • select f.Category,f.Name,(f.Bigbale_QTY-f.Dispatch_QTY) as [Balance],(f.Bigbale_weight-f.Dispatch_Weight) as [W_Balance] from (
    select e.Category,e.Name,
    
    isnull(min(e.[Bigbale_QTY]),0) as [Bigbale_QTY],isnull(min(e.[Bigbale_weight]),0) as [Bigbale_weight],isnull(sum(c.QTY),0) as [Dispatch_QTY],isnull(sum(c.Bweight),0) as [Dispatch_Weight] from (
    select ca.CName as Category, ca.CID,
    
    a.Descriptionitem as Name,min(a.CodeItem) as CodeItem,isnull(sum(b.Bpqty),0) as [Bigbale_QTY],isnull(sum(b.Bweight),0) as [Bigbale_Weight]
    from ItemMasterFile a
    inner join Catagory ca on ca.CID=a.CID
    left join Bigbalprd b on a.CodeItem=b.CodeItem
     where a.Packsize ='bigbale' and b.delID is null  and (b.trans is null or b.Trans='b') 
      group by a.Descriptionitem, ca.CName, CA.CID) e
    left join Dispatch_BD c on e.CodeItem=c.CodeItem 
     where c.Delidd is null  
    group by e.Name,e.Category
    
    )f ORDER BY f.CID
    In other words, that column needs to be part of the inner subquery return list of columns.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Marked as answer by Akhterhussain Thursday, July 9, 2020 6:12 PM
    Thursday, July 9, 2020 6:09 PM
  • Read any book on RDBMS; a table is an unordered set of rows and it must have a key. The result of any query will be a table and thus will have no order. When you put in order by clause at the end of a completed query, you've turned it from a table into a local cursor; a cursor is essentially a simple sequential file. You cannot do a join on a file and a table. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Saturday, July 11, 2020 6:23 PM