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
ENDUPDATE
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.
4条答案
按热度按时间nsc4cvqm1#
The
order byneeds to be in the statement that selects from the function.This is the only way to get reliable results that are assured to not suddenly break in the future .
sgtfey8w2#
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...e0uiprwp3#
You can duplicate your result table (declare a table
var @Xand@ret_X).Then perform your actions on the @X table and make the following statement as last statement in your function.
This gives me the sorting I want.
34gzjxbg4#
Best way is to return your data from the back end and do the sorting Using a linq query in you c sharp code