I want ascending wise data CID wise

    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
left join Dispatch_BD c on e.CodeItem=c.CodeItem 
 where c.Delidd is null  
group by e.Name,e.Category


I tried below but error is coming ( ORDER BY CA.CID ASC) 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.


This is an error and No, you can't use ORDER BY to sort rows in the derived table for the reason given in the error message. The order of rows in the result set is ultimately controlled by an ORDER BY clause in the outer SELECT, not by the OVER clause. The OVER clause "specifies the logical order in which the window functioncalculation is performed". It doesn't sort the result set. There are workarounds for this issue though and you can check solution proposed at https://stackoverflow.com/questions/18031421/the-order-by-clause-is-invalid-in-views-inline-functions-derived-tables-subqu either by using ROW_NUMBER function or ORDER BY column OFFSET 0 ROWS.

Alternative method is explained by Mr. Pinal Dave at this link.

Hope this helps.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.