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

sql zoo
sql tutorial
sql server problems and solutions
sql practice problems pdf
oracle sql practice exercises with solutions
learn sql
sql online practice test
sql prep

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.

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;

How To Solve SQL Problems, In this video I will be solving all of the SQL problems in my Learn SQL GitHub repository. I will Duration: 20:11 Posted: Nov 24, 2018 TheAnswerHub is a top destination for finding answers online. Browse our content today! Find free sql databases on TheAnswerHub.com.

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

SQL Exercises, Practice, Solution, The issue of performance is a typical aspect of SQL servers that tends to slip through the cracks, whether it is during initial onboarding or the  Find Database Sql Now at Internetcorkboard.com! Find Database Sql. Search Now

I used the following construction:

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

Solve SQL, Read 50 most frequently asked SQL query questions and improve your SQL skills. To get you started, we've answered with appropriate SQL queries. Search for Database Sql. Browse & Discover Useful Results! Browse Relevant Sites & Find Database Sql. All Here!

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.

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.

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.

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

5 Typical SQL Server Problems: How to Get Rid of Them , of problems with SQL Server. That doesn't mean SQL Server doesn't do indexes well. These days SQL Server does indexing quite well, actually. No, the issue with indexes and SQL Server have to do with how easy it easy for users to make mistakes with regards to indexing. What is SQL? 1. Modify the query above so that it only includes orders placed on or after Jan 1, 1997. You will need to reference the 2. Modify the query (use the original query at the top of the page, not the modified one for problem 1) so that it 3. The following query is a little more

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
    )

50 SQL Query Questions You Should Practice for Interview, using your own data. MySQL is a popular free database that is compatible with most operating systems. This handy two-page list outlines 10 familiar SQL Server problems and an assortment of solutions. From "Out of Space" errors to a database going suspect, this list is great quick reference for

The Top 5 Most Common Problems With SQL Server, , or practice on a sample fiddle. If you like JSFiddle this is a similar tool. The SQL Profiler trace captures the activity on the computer running SQL Server. You can use this information to identify slow running queries and non-optimal execution plans. Additionally, SQL Profiler documents the series of events that occur before the performance problem and helps to identify its cause.

The Best Way to Learn SQL (From IT Training Experts), Once there, you can use the dbcc inputbuffer command to see the SQL statement that the SPID is running. This will point you toward the problem's cause.Join this  SQL can seem limited when you first start out because you haven’t approached a diverse set of questions. Once you get past JOINs, Loops, and other basic concepts in SQL, like subqueries

Improving your SQL skills |> News and podcasts for developers , Tutorials: Learn SQL in stages Programming with SQL: [ SQL in perl on the Web | SQL from Java ]; Error Messages: Some error messages from SQL engines​  Indexes are the number one cause of problems with SQL Server. That doesn’t mean SQL Server doesn’t do indexes well. These days SQL Server does indexing quite well, actually. No, the issue with indexes and SQL Server have to do with how easy it easy for users to make mistakes with regards to indexing.

Comments
  • what don't you understand about the error message?
  • 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.
  • @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.
  • @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.
  • Almost there! Now I receive the error: Incorrect syntax near ';'.
  • @Ben: Fixed the query. Added alias on the subquery.
  • Hi Biggs, I tried removing the ORDER BY in the IN statements and that has still left me with an error - "Incorrect syntax near ';'."
  • @Ben - I just posted an edit with the code you should try. Give that a shot. You had also missed one parenthesis so I fixed that. If you want the top-most query to have an ORDER BY, put it after all of the code I listed and it will work fine.
  • Thanks for the edit Biggs - I receive an error still: Incorrect syntax near ')'.