I wrote this line in mysql and it works

SELECT
    IDKartoch,
    SUM(SummOpl) AS Total
FROM
    Oplata
GROUP BY 
    IDKartoch
HAVING
    SUM( Oplata.SummOpl ) >= 0
ORDER BY
    IDKartoch;

And I get this result:

But after I remove the IDKartoch column from the SELECT...

SELECT
    SUM(SummOpl) AS Total
FROM
    Oplata
GROUP BY
    Oplata.IDKartoch 
HAVING
    SUM( Oplata.SummOpl ) >= 0
ORDER BY
    Oplata.IDKartoch;

I get this error

Function or column reference to 'IDKartoch' in the ORDER BY clause its invalid SQLCODE=-854

CodePudding user response：

• The syntax ORDER BY Oplata.IDKartoch has a fully qualified reference to the Oplata "BASE TABLE" (as opposed to a VIEW or derived-table).

• However, when you use GROUP BY in a query your query now represents an anonymous derived-table, which is what ORDER BY sees, and this derived-table is not the same object as Oplata.

  • That's why it complains about the missing column: the column simply doesn't exist anymore.

• Anyway, you can order rows by a column that isn't in the SELECT clause, but it's a bit gnarly....

  • Also, note that (in ISO SQL, I don't know about MySQL), the ORDER BY clause can only be used in the outermost (non-CTE) SELECT query step of a nontrivial query (because relations are sets, which are unordered).

The trick is to take the existing ( IDKartoch, Total ) query-result (actually "derived table") from your current query, and wrap it in an outer-query which aliases the derived-table (allowing ORDER BY IDKartoch while only having Total in the outermost SELECT clause.

Like so (tested in MySQL 5.6):

SELECT
    q.Total
FROM
    (
        SELECT
            IDKartoch 
            SUM(SummOpl) AS Total
        FROM
            Oplata
        GROUP BY
            IDKartoch 
        HAVING
            SUM( Oplata.SummOpl ) >= 0
    ) AS q
ORDER BY
    q.IDKartoch;

Here's a SQLFiddle demonstrating a similar SELECT query that is able to sort the rows by a "hidden" column hiddenSortColumn.

CodePudding user response：

You just removed the column that you wanted to ORDER BY.