SQL Error "The ORDER BY clause is invalid in views..."

Question

I have a SQL Server error I'm trying to resolve. Could someone please help me out?

The query is:

SELECT TOP 10 * 
FROM ( 
SELECT c.id, c.name, c.inserteddate, c.cityname, ftblstates.name AS statename, clc.name AS catname, '' AS listingimagelogo, '' AS orgname, relocateyn, '' AS employerclassified
FROM ((tblclassifieds c 
LEFT JOIN tblclassifiedscategories clc ON c.categoryid = clc.id) 
LEFT JOIN ftblstates ON c.stateid = ftblstates.id) 
WHERE (c.expirydate != '') AND NOT c.id IN ( 
SELECT TOP 10 tblclassifieds.id 
FROM tblclassifieds 
WHERE (c.expirydate != '') 
ORDER BY inserteddate desc) 
UNION ALL 
SELECT ce.id, ce.name, ce.inserteddate, suburb AS cityname, ftblstates.name AS statename, ce.jobtype AS catname, ce.listingimagelogo, ce.orgname, '' AS relocateyn, '1' AS employerclassified 
FROM tblclassifiedemployers ce 
LEFT JOIN ftblstates ON ce.stateid = ftblstates.id 
WHERE (ce.expirydate != '') AND NOT ce.id IN ( 
SELECT TOP 10 tblclassifiedemployers.id 
FROM tblclassifiedemployers 
WHERE (ce.expirydate != '') 
ORDER BY inserteddate desc) 
ORDER BY inserteddate desc;

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.

what don't you understand about the error message? – Mat May 10 '11 at 06:10 — Mat, May 10 '11 at 06:10

score 37 · Answer 1 · answered Jan 10 '19 at 22:30

37

If you are using SQL Server 2012 or higher version, please use "offset 0 rows" after order by. Ex -

create view Department_View
as
select Name from [HumanResources].[Department]
order by Name offset 0 rows

answered Jan 10 '19 at 22:30

Mini

447
5
16

hallie · Accepted Answer · 2011-05-10T06:33:29.740

4

As stated ORDER BY must not apper in subqueries unless TOP or FOR XML is used.

    SELECT TOP 10 * FROM ( 
    SELECT 
        c.id, 
        c.name, 
        c.inserteddate, 
        c.cityname, 
        ftblstates.name AS statename, 
        clc.name AS catname, 
        '' AS listingimagelogo, 
        '' AS orgname, relocateyn, 
        '' AS employerclassified
    FROM tblclassifieds c 
    LEFT JOIN tblclassifiedscategories clc ON c.categoryid = clc.id
    LEFT JOIN ftblstates ON c.stateid = ftblstates.id 
    WHERE c.expirydate != ''
    AND NOT c.id IN ( 
        SELECT TOP 10 
            tblclassifieds.id 
        FROM tblclassifieds 
        WHERE c.expirydate != ''
        ORDER BY inserteddate desc 
    ) 
    UNION ALL
    SELECT 
        ce.id, 
        ce.name, 
        ce.inserteddate, 
        suburb AS cityname, 
        ftblstates.name AS statename, 
        ce.jobtype AS catname, 
        ce.listingimagelogo, 
        ce.orgname, '' AS relocateyn, 
        '1' AS employerclassified 
    FROM tblclassifiedemployers ce 
    LEFT JOIN ftblstates ON ce.stateid = ftblstates.id 
    WHERE ce.expirydate != ''
    AND NOT ce.id IN ( 
        SELECT TOP 10 
            tblclassifiedemployers.id 
        FROM tblclassifiedemployers 
        WHERE ce.expirydate != ''
        ORDER BY inserteddate desc
    )
) a ORDER BY inserteddate desc;

edited May 10 '11 at 06:33

answered May 10 '11 at 06:14

hallie

2,760
19
27

By removing the two extra order by clauses I receive this 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. – Ben May 10 '11 at 06:19
@hallie - ORDER BY can appear in sub-queries. You just need to have TOP or FOR XML in the sub-query, which the OP seems to have. – IAmTimCorey May 10 '11 at 06:20
@Biggs: Sorry my bad, I updated the script. Sorry I cannot run as I don't have the data. @Ben: Can you try again? I removed some parenthesis. – hallie May 10 '11 at 06:22
Almost there! Now I receive the error: Incorrect syntax near ';'. – Ben May 10 '11 at 06:25
@Ben: Fixed the query. Added alias on the subquery. – hallie May 10 '11 at 06:34
I believe this is now fixed! The example above works correctly :) Thanks Hallie and Biggs – Ben May 10 '11 at 06:48
@Ben: I'm not seeing any errors on my end when I parse the query using SQL2K8. Can you check your systax? – hallie May 10 '11 at 06:49

score 1 · Answer 3 · answered May 03 '17 at 15:09

1

I used the following construction:

SELECT
    ROW_NUMBER() OVER (ORDER BY LASTNAME) SORTORDER,
    *
FROM
    CLIENT

answered May 03 '17 at 15:09

AJW

36
3

score 1 · Answer 4 · answered Apr 27 '21 at 18:08

1

Use this:

create view Department_View
as
select Name from [HumanResources].[Department]
order by Name offset 0 rows

answered Apr 27 '21 at 18:08

Daniel Viveros

11
2

score -5 · Answer 5 · edited Dec 16 '16 at 13:04

CREATE FUNCTION GetUnitIDWithScenarioCount
(
@calculatorType int
)
returns TABLE as
return
(
select count,unitid from(
 SELECT Top 1
         count(sc.UnitId) as count, sc.unitid
           FROM scenarios SC 
    INNER JOIN npcstatus NPC
        ON NPC.UnitId=SC.UnitId
    INNER JOIN IPEDSCollegeData..hd hd
        ON hd.UnitId=NPC.UnitId
        WHERE npc.calculatorType=4
        Group by sc.unitid
    ) as temp
    order by count
    )

SQL Error "The ORDER BY clause is invalid in views..."

5 Answers5

Linked