Order By In a SQL Table Valued Function

Question

I've read about this problem on a few different sites, but I still don't understand the solution. From what I understand, SQL will optimize the query in the function and sometimes the Order By clause will be ignored. How can you sort results?

How can I sort results in a simple table valued function like this?

Create function [dbo].fTest

--Input Parameters
(@competitionID int)

--Returns a table
RETURNS @table TABLE (CompetitionID int )

as

BEGIN
    Insert Into @table (CompetitionID)
    select CompetitionID from Competition order by CompetitionID desc
    RETURN
END

UPDATE

I found inserting a primary key identity field seems to help (as mentioned in the answer posted Martin Smith). Is this a good solution?

--Returns a table
RETURNS @table TABLE
(
    SortID int IDENTITY(1,1) PRIMARY KEY,
    CompetitionID int 
)

In reference to Martin's answer below, sorting outside of the select statement isn't that easy in my situation. My posted example is a stripped down version, but my real-life issue involves a more complicated order by case clause for custom sorting. In addition to that, I'm calling this function in an MVC controller with a LINQ query, which means that custom sorting would have to be added to the LINQ query. That's beyond my ability at this point.

If adding the identity field is a safe solution, I'm happy to go with that. It's simple and easy.

Regarding your edit no that isn't a good solution. The only guaranteed solution is to use an `order by` in the outer `select`. It is entirely possible that the table variable (with implicit `nolock` hint) can get an allocation ordered scan and return rows out of key order for example. The purpose of adding an ID column there was to get something that could be referenced by an outer `order by` not replace it. You should ask another question about how to get your Linq query to add the required `order by` rather than just ignore it and hope the TVF maintains some order without it. — Martin Smith, Jul 18 '15 at 23:40
Thanks, I'll have to put up another question about the linq sorting. — madvora, Jul 19 '15 at 00:08

score 14 · Accepted Answer · edited May 23 '17 at 12:25

14

The order by needs to be in the statement that selects from the function.

SELECT CompetitionId
FROM [dbo].fTest()
ORDER BY CompetitionId

This is the only way to get reliable results that are assured to not suddenly break in the future.

edited May 23 '17 at 12:25

Community

1
1

answered Jul 18 '15 at 23:13

Martin Smith

438,706
87
741
845

Thank you for the response. Please see my updated post. I'd like to hear your opinion. – madvora Jul 18 '15 at 23:32

score 0 · Answer 2 · edited Apr 21 '18 at 10:34

0

You can duplicate your result table (declare a table var @X and @ret_X).

Then perform your actions on the @X table and make the following statement as last statement in your function.

 insert into @ret_X 
 select top 10000 * from @X
 order by (column_of_choise) desc

This gives me the sorting I want.

edited Apr 21 '18 at 10:34

Robert

5,703
2
31
32

answered Apr 21 '18 at 10:05

Wouter

1

score -3 · Answer 3 · answered Nov 18 '19 at 08:43

-3

Best way is to return your data from the back end and do the sorting Using a linq query in you c sharp code

answered Nov 18 '19 at 08:43

Houssam

1

3

No. If SQL Server can return you the results sorted the way you want, with a simple `ORDER BY` clause, why go through the hassle of letting another environment do it? – TT. Nov 18 '19 at 10:17

Order By In a SQL Table Valued Function

3 Answers3

Linked