CODEWITHSUNDEEP
sqlsql-server
Lukáš Václavek
Lukáš Václavek

Reputation: 452

SQL ORDER BY in SQL table returning function

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 )

Upvotes: 0

Views: 795

Answers (4)

GuidoG
GuidoG

Reputation: 12014

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.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269483

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.

Upvotes: 2

Amir Molaei
Amir Molaei

Reputation: 3810

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 )

Upvotes: 2

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

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

Upvotes: 1

Related Questions