SQL 问题“视图中的 ORDER BY 子句无效...”

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

提问by Ben

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

我正在尝试解决 SQL Server 错误。有人可以帮我吗？

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.

并且错误：ORDER BY 子句在视图、内联函数、派生表、子查询和公共表表达式中无效，除非还指定了 TOP 或 FOR XML。

采纳答案by hallie

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

如上所述，除非使用 TOP 或 FOR XML，否则 ORDER BY 不得出现在子查询中。

    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;

回答by Mini

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

如果您使用的是 SQL Server 2012 或更高版本，请在 order by 后使用“偏移 0 行”。前任 -

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

回答by AJW

I used the following construction:

我使用了以下结构：

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

回答by IAmTimCorey

I think it is self-explanatory. You can't use the ORDER BY clause in your sub-queries because they don't use a TOP or FOR XML.

我认为这是不言自明的。您不能在子查询中使用 ORDER BY 子句，因为它们不使用 TOP 或 FOR XML。

When reviewing your code, I'm not sure I see the problem code though. All of the code looks correct. You are only using the ORDER BY in sub-queries that also employe the TOP command so they should all work. I would comment out all of the ORDER BY commands and then add them in one at a time until you find the one that throws the error. Maybe it doesn't like them because they are a sub-query of a sub-query.

在查看您的代码时，我不确定我是否看到了问题代码。所有的代码看起来都是正确的。您仅在也使用 TOP 命令的子查询中使用 ORDER BY，因此它们应该都可以工作。我会注释掉所有 ORDER BY 命令，然后一次添加一个，直到找到引发错误的那个。也许它不喜欢它们，因为它们是子查询的子查询。

Edit: Looking over it again, I think the problem is your IN statements. I don't believe you can use a sub-query with the ORDER BY when you are using IN.

编辑：再看一遍，我认为问题在于你的 IN 语句。我不相信您在使用 IN 时可以使用带有 ORDER BY 的子查询。

Here is the code I would try:

这是我会尝试的代码：

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 != '')) 
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 != '')))

回答by Ravi

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
    )