0

I am running the below code. When I run this code, I get error message:

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.

I'm not sure why I am getting this error message. Help is much appreciated.

SELECT  a.DELINQ_BUCKET_GROUP, a.vv_count
FROM 
(   
    SELECT  DELINQ_BUCKET_GROUP, 
            CASE WHEN DELINQ_BUCKET_GROUP IS NULL THEN 
                    SUM(CASE WHEN DELINQ_BUCKET_GROUP IS NULL THEN 1 ELSE 0 END)
                ELSE COUNT(DELINQ_BUCKET_GROUP) 
            END AS vv_count 
    FROM    DCSReporting.dbo.DIM_DELINQUENT_BUCKET
    GROUP BY DELINQ_BUCKET_GROUP
    ORDER BY vv_count DESC
) a
WHERE rownum<=100
GarethD
  • 68,045
  • 10
  • 83
  • 123
user3149018
  • 1
  • 1
  • 5
    The error message is very clear; you can't use `order by` in a sub-query. Also, there is no point to use it, a sub-query is just a set as any other. Why would you want to sort an intermediate set? You want to order your result set. – HoneyBadger Apr 18 '16 at 14:58
  • 1
    rownum is an oracle feature. Are you really sure you're working with SQL Server? – James Z Apr 18 '16 at 15:00
  • 2
    The next error you are likely to encounter (invalid column name rownum) is because you don't have a column named rownum in your derived table. – Sean Lange Apr 18 '16 at 15:01
  • 1
    Unless you are trying to create a derived table on the top 100 records ordered by vv_count??? In that case you would need to add a `TOP (100)` operator between your `SELECT` and `DELINQ_BUCKET_GROUP`... – Shaneis Apr 18 '16 at 15:19
  • Possible duplicate of [Order BY is not supported in view in sql server](http://stackoverflow.com/questions/26888472/order-by-is-not-supported-in-view-in-sql-server) – whereisSQL Apr 19 '16 at 15:21

1 Answers1

0

If you are using SQL Server 2012 or later version, use Offset 0 Rows after Order By:

SELECT Id,
       Name       
FROM Table

ORDER BY Id 
OFFSET 0 ROWS
Murat Yıldız
  • 11,299
  • 6
  • 63
  • 63