14

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.

Improve this question
2
  • 2
    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. Commented Jul 18, 2015 at 23:40
  • Thanks, I'll have to put up another question about the linq sorting. Commented Jul 19, 2015 at 0:08

5 Answers 5

Reset to default
15

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.

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

1 Comment

Thank you for the response. Please see my updated post. I'd like to hear your opinion.
0

This is just a bit of an ingenious workaround and only useful in some scenarios but it worked beautiful for what I needed: you can create a stored procedure that does a select * from the function with the desired order by clause and use that instead...

Improve this answer

1 Comment

This is a solution provided you only ever want that function returned that way. Not really the best solution but it will accomplish what you want it to, albeit in a much more convoluted manner than the actual solution.
0

Since iTVFs perform generally speaking better than multi statement functions, my workaround was to use a iTVF with an order by in the select statement and select 99.999 PERCENT WITH TIES. That way, the ordering works. Not saying that this is the best approach. I would agree to better add the sorting to the calling query.

Improve this answer

Comments

-1

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.

Improve this answer

1 Comment

It is a table valued function. Why another insert? Just sort the data the function returns how you want it. This is creating and using additional resources that are not needed.
-4

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

Improve this answer

2 Comments

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?
SQL Server can sort this data more efficiently than the application can. Why waste app cycles on something that can be done much faster elsewhere?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.