SQL ORDER BY in SQL table returning function

Question

so I have simple function trying to get two fields from database. I'm trying to use order by for the results however I cannot use ORDER BY in return clause. It tells me

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.

Is is it possible to use ORDER BY in RETURN statement? I would like to avoid using order by when executing the function.

CREATE FUNCTION goalsGames1 () RETURNS TABLE
AS RETURN(
    SELECT MAX(goals_scored) goals,
           no_games
    FROM Player
    GROUP BY no_games
    ORDER BY no_games DESC )

The correct place for the ORDER BY is in the SELECT from goalsGames1 — Martin Smith
– Martin Smith, Commented Mar 11, 2019 at 10:22
Sorry, I have edited the question. Does that mean that only place where can I use order by is in the SELECT from goalsGames1 ? — Lukáš Václavek
– Lukáš Václavek, Commented Mar 11, 2019 at 10:23
yes that is what it means. The statement that executes the query is the one that is receiving the rows, and therefor only that can order them — GuidoG
– GuidoG, Commented Mar 11, 2019 at 11:04

Amir Molaei · Accepted Answer · 2019-03-11 10:31:29Z

2

One trick to skip this error is using top as it is mentioned in the error message:

CREATE FUNCTION goalsGames1 () RETURNS TABLE
AS RETURN(
    SELECT Top 100 Percent MAX(goals_scored) goals,
           no_games
    FROM Player
    GROUP BY no_games
    ORDER BY no_games DESC )

answered Mar 11, 2019 at 10:31

Amir Molaei

3,8601 gold badge20 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Lukáš Václavek Over a year ago

Thanks, this is exactly the thing I was searching for.

Gordon Linoff Over a year ago

This does not solve the problem, it merely hides the syntax error. Read the documentation!

Dan Guzman Over a year ago

@LukášVáclavek, be aware that the ORDER BY may not be honored as @GordonLinoff suggested. You must specify the ORDER BY in the query using a function (or view) to guarantee ordering of results.

Martin Smith Over a year ago

Especially Top 100 Percent is optimised out anyway and has no effect. Even for values of TOP that are not optimised out the correct solution is to do it in the select from the function if you want guaranteed results

Gordon Linoff · Accepted Answer · 2019-03-11 10:37:40Z

2

I would like to avoid using order by when executing the function.

If you are using the function and want the results in a particular order, then you need to use ORDER BY.

This is quite clearly stated in the documentation:

The ORDER clause does not guarantee ordered results when a SELECT query is executed, unless ORDER BY is also specified in the query.

answered Mar 11, 2019 at 10:37

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Comments

Zaynul Abadin Tuhin · Accepted Answer · 2019-03-11 10:24:11Z

1

use order by intimes of selection your function not in times of creation

so use here in select * from goalsGames1 order by col

and your error tells you where order by is invalid

answered Mar 11, 2019 at 10:24

Zaynul Abadin Tuhin

32.1k6 gold badges37 silver badges66 bronze badges

2 Comments

Lukáš Václavek Over a year ago

Thank you for the answer. Does this mean that I cannot use ORDER BY in function definition?

Zaynul Abadin Tuhin Over a year ago

@LukášVáclavek yes you can not

GuidoG · Accepted Answer · 2019-03-11 11:06:48Z

1

You cannot order by inside a function, the idea is to order the resultset returned by the function.

select * 
from   dbo.goalsGames1() 
order by no_games

Even if you would order by inside the function, there is no guaranty that this ordering would be preserved when the resultset is returned. The executing query (select * from functionname) has to be responsible for setting the order, not the function or view.

Who ever receives the rows is the only one that can order them, so in this case, the select * from goalsGames1() is the receiver, and this query has to order the results.

edited Mar 11, 2019 at 11:06

answered Mar 11, 2019 at 10:43

GuidoG

12.3k6 gold badges59 silver badges101 bronze badges

Collectives™ on Stack Overflow

SQL ORDER BY in SQL table returning function

4 Answers 4

4 Comments

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related