SQL Order by the parameter value from scalar-valued function without including it into select

Question

I have a query of the structure

SELECT DISTINCT
--parameters
[ProjectNum] = dbo.ProjNumFunction(P.A, P.B, P.C, P.D, P.E)
--other parameters
FROM dbo.Project P
WHERE --conditions
ORDER BY --parameter

ProjNumber function simply takes values of A, B, C, D, and E from the Project table, and concatenates them into a single string, which looks like 'A B C D E'.

I need to order by one of these parameters inside the function WITHOUT placing them into my select list. Is it possible?

The following thread (and the second) were the closest I got, but it is not what I need.

EDIT

I have tried using statements below, and the results are

ORDER BY SUBSTRING(ProjectNumber, 9, 13) desc --Invalid column name error

and

[ProjectNumber] = CONCAT(P.A, P.B, P.C, P.D, P.E) 
-- it concatinated successfully, but still does not allow me to order by any of those 
-- parameters without including onto select list

Is dbo.ProjNumFunction(P.A, P.B, P.C, P.D, P.E) passed as one string (missing quotes) or 5 parameters — John Cappelletti, Jul 28 '16 at 21:53
@JoeC Something like ORDER BY ProjNumFunction(P.A) or something? — Vadzim Savenok, Jul 28 '16 at 21:54
Sorry Vadzim I didnt notice the distinct so I deleted my comment. I will post a possible solution in case Gordons dont work out. — Joe C, Jul 28 '16 at 22:00
@JoeC Not a problem =) However, I need distinct values, so unfortunately, Gordon's solution wouldn't work for me. — Vadzim Savenok, Jul 28 '16 at 22:01

score 0 · Answer 1 · answered Jul 28 '16 at 21:50

0

No, you cannot (well, without complex string manipulation). You can, however, do the following:

SELECT ProjectNum
FROM dbo.Project P OUTER APPLY
     (VALUES (dbo.ProjNumFunction(P.A, P.B, P.C, P.D, P.E)) x(ProjectNum)
WHERE --conditions
GROUP BY ProjectNum
ORDER BY MAX(A);

That is, if you replace SELECT DISTINCT with GROUP BY, then you can use aggregation functions in the ORDER BY.

answered Jul 28 '16 at 21:50

Gordon Linoff

1,242,037
58
646
786

I need a distinct however. So, there is no other way for that matter? – Vadzim Savenok Jul 28 '16 at 21:52
The `GROUP BY` does the same thing. – Gordon Linoff Jul 28 '16 at 22:37

Joe C · Accepted Answer · 2016-07-28T23:12:31.170

0

EDIT 3:

I believe this would work. The idea is to first create a subquery defining your sort field and the data you want to retrieve. The second step is to assign a sortorder to every record based on the sort field. This is your primary sort. The next field will assign a 1 to each record in the group and the order by can give additional options as which record to keep in a group if your requirements were more complex.

The outer query does not need distinct or group by as the window functions take care of that. I do not have access to a test environment so the syntax and logic may be off a bit but I think this will work for you.

Select Data, 
   From
      (
       Select Data, 
              Row_Number() Over (Order By SortField) TopLevelSort,
              Row_Number() Over (Partition By Data Order By SortField) Keepers       
         From
             (Select SUBSTRING(dbo.ProjNumFunction(P.A, P.B, P.C, P.D, P.E), 9, 13) SortField,
                   dbo.ProjNumFunction(P.A, P.B, P.C, P.D, P.E), 9, 13) Data
                  From dbo.Project P
                  Where --conditions
              ) A
       ) B
    Where Keepers = 1
    Order By TopLevelSort

edited Jul 28 '16 at 23:12

answered Jul 28 '16 at 22:01

Joe C

3,925
2
11
31

While I try not to mess with other functions, since it will interrupt the workflow with others, but your answer gave me an idea I want to try. – Vadzim Savenok Jul 28 '16 at 22:03
I understand the need to use code that already exists without being able to change it. I am glad you have an idea, good luck ! – Joe C Jul 28 '16 at 22:07
Initially I tried `SUBSTRING(ProjectNumber, 9, 13)`, but was throwing an error of invalid argument. Then, based on your suggestion, I replaced function usage with `[ProjectNumber] = CONCAT(P.A, P.B, P.C, P.D, P.E)`. Still no dice =( – Vadzim Savenok Jul 28 '16 at 22:16
If you do a substring on the function it should work. Not sure what you mean about the concat not working did you get an error? Best bet is to update your OP with those two queries you tried. – Joe C Jul 28 '16 at 22:18
Just tried it, still returns `ORDER BY items must appear in the select list if SELECT DISTINCT is specified.` – Vadzim Savenok Jul 28 '16 at 22:32
Doh, your right of course. Looks like Row_Number() is the way to go. I will come up with an example. – Joe C Jul 28 '16 at 22:35

John Cappelletti · Answer 3 · 2016-07-28T23:11:09.647

Here is a little approach to sort a delimited string. Perhaps it can be modified for your function.

Currently I have a limit of 9 Object. This can be expanded or contracted to suite your needs

Edit - Removed the need for the UGLY Union Alls

Declare @String     varchar(200) = 'P.C, P.A, P.E, P.D, P.B, P.A'
Declare @Delimeter  varchar(25)  = ','
Declare @NewString  varchar(200) = ''
Declare @NewDelimer varchar(25)  = ' '

Declare @XML xml
;with cteBase as (
    Select Pos1 = xDim.value('/x[1]','varchar(250)')
          ,Pos2 = xDim.value('/x[2]','varchar(250)')
          ,Pos3 = xDim.value('/x[3]','varchar(250)')
          ,Pos4 = xDim.value('/x[4]','varchar(250)')
          ,Pos5 = xDim.value('/x[5]','varchar(250)')
          ,Pos6 = xDim.value('/x[6]','varchar(250)')
          ,Pos7 = xDim.value('/x[7]','varchar(250)')
          ,Pos8 = xDim.value('/x[8]','varchar(250)')
          ,Pos9 = xDim.value('/x[9]','varchar(250)')
     From (Select Cast('<x>' + Replace(@String,@Delimeter,'</x><x>')+'</x>' as XML) as xDim) A
)
Select @XML = (Select * from cteBase for XML RAW)
Select @NewString = @NewString+@NewDelimer+ltrim(rtrim(Value)) 
 From (Select Distinct Top 100 Percent 
              Value = ltrim(rtrim(Attr.value('.','varchar(max)') )) 
        From  @XML.nodes('/row') as A(r) 
        Cross Apply A.r.nodes('./@*') AS B(Attr)
        Order By 1) A

Select @NewString

Returns

 P.A P.B P.C P.D P.E

Notice the list is DISTINCT and SORTED from the original

Sorry for a late response, I am simply trying to wrap my head around it. — Vadzim Savenok, Jul 28 '16 at 22:30
I will definitely give it a try, though I doubt I will be able to respond today with results. — Vadzim Savenok, Jul 28 '16 at 22:35
@VadzimSavenok we take a string, convert it into XML (9 fields ... I got bored). Then we take that row and normalize via a series of Union All's (I have another process which may be cleaner). And then the final step we re-create the sorted/distinct string — John Cappelletti, Jul 28 '16 at 22:35
@VadzimSavenok Updated my answer to remove all the UGLY Union All's — John Cappelletti, Jul 28 '16 at 22:58

SQL Order by the parameter value from scalar-valued function without including it into select

EDIT

3 Answers3