CODEWITHSUNDEEP
sql-server
ylleigh12
ylleigh12

Reputation: 147

SQL Error on Order by and group by

This SQL works in mysql but I can't do this in SQL Server.

SELECT COUNT(*) 
FROM (
    SELECT 
        COUNT(postnID) AS Total, 
        postnID, 
        Unit_DBM, 
        job_type, 
        level, 
        internal_plantilla, 
        INCID, 
        ITEM_NO_2005, 
        position_type, 
        position_status 
    FROM paf_plantilla 
    GROUP BY 
        internal_plantilla, 
        level, 
        INCID, 
        postnID, 
        position_status 
    ORDER BY 
        internal_plantilla, 
        postnID
) AS num

Error:

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.

Upvotes: 0

Views: 92

Answers (2)

LoztInSpace
LoztInSpace

Reputation: 5697

This won't work in any non MySQL implementation of SQL.

  • Non aggregates in aggregate queries must be grouped by
  • ORDER BY without TOP in a subquery is most likely not going to do what you think it will (may not give an error but it's not right either)

Because of the first point it's very hard to work out what the correct query - some kind of count of combinations of other things. If you explain what I might be able to update the answer.

Upvotes: 2

Amit Sukralia
Amit Sukralia

Reputation: 950

You can't select columns which are not in the group by clause. Since you are doing a count, you really don't need the order by clause. Try the below: 

SELECT COUNT(*) 
FROM (
    SELECT 
        COUNT(postnID) AS Total, 
        postnID, 
        Unit_DBM, 
        job_type, 
        level, 
        internal_plantilla, 
        INCID, 
        ITEM_NO_2005, 
        position_type, 
        position_status 
    FROM paf_plantilla 
    GROUP BY 
        postnID, 
        Unit_DBM, 
        job_type, 
        level, 
        internal_plantilla, 
        INCID, 
        ITEM_NO_2005, 
        position_type, 
        position_status 
) AS num

Upvotes: 0

Related Questions