Order By Combined

Question

I have a result set where I want to apply a combined sort combined.

Example

SELECT Id, Name FROM Users

Result

Id  Name   
1   Albert
2   Alfred
3   Carl
4   David
5   Ernest

Now those users could be orderd by last access, last post, number of comments made or calculated parameters. How could i order if i have a SELECT like this:

SELECT Id, Name, function_last_access(Id) as f1, function_last_post(Id) as f2, function_calculate_parameters(Id) as f3 FROM Users

Id  Name     f1   f2   f3
1   Albert   2    100  1,1
2   Alfred   10   20   2
3   Carl     0    15   5
4   David    5    2    3
5   Ernest   4    5    1

It's better to take different ordered lists and combine them after? Or put them in columns and then apply a normalization function and then sort them?

Could help me ORDER BY PARTITION or something like that?

Please show the expected output, it's not clear what you are trying to achieve. — Daniel Hilgarth, Jan 16 '13 at 14:43
I must first normalize functions to a single factor so as to make them comparable. In the sense that for f1 top could be 10, but for f2 top could be 0 and f3 could be 1,1... Once normalized to a single scale could then add them and order them for example. — Luigi Saggese, Jan 16 '13 at 14:59
I don't know until i give a weighed to my function as show in Adomar's answer. — Luigi Saggese, Jan 16 '13 at 15:04

Andomar · Accepted Answer · 2013-01-16T14:56:09.253

2

You can move the aliases to a subquery so you can refer to them in the order by:

SELECT  *
FROM    (
        SELECT  Id
        ,       Name
        ,       function_last_access(Id) as f1
        ,       function_last_post(Id) as f2
        ,       function_calculate_parameters(Id) as f3 
        FROM    Users
        ) as SubQueryAlias
order by
        0.3 * f3 + 0.2 * f2 + 0.1 * f3 desc

edited Jan 16 '13 at 14:56

answered Jan 16 '13 at 14:44

Andomar

232,371
49
380
404

If i make an order like that i give more value to f1 than f2 and f3. I'd like to normalize value on same standard and than order. – Luigi Saggese Jan 16 '13 at 14:55
1

If the functions return a number, you could use a weighed average, updated in answer. Not that user-defined functions typically do not perform well enough to be useful in a search algorithm. A CLR function might perform well enough, but is not trivial to write and deploy. – Andomar Jan 16 '13 at 14:57

score 1 · Answer 2 · edited Feb 15 '13 at 18:42

1

Would this work for you?

SELECT Id, Name, f1, f2, f3
FROM 
(
  SELECT Id, 
    Name, 
    function_last_access(Id) as f1, 
    function_last_post(Id) as f2, 
    function_calculate_parameters(Id) as f3 
  FROM Users
) f
ORDER BY f1, f2, f3

edited Feb 15 '13 at 18:42

wickedone

542
1
6
18

answered Jan 16 '13 at 14:44

sgeddes

62,311
6
61
83

If i make an order like that i give more value to f1 than f2 and f3. I'd like to normalize value on same standard and than order. – Luigi Saggese Jan 16 '13 at 14:55
I think I'd need to see your desired output -- a little unclear as to what you want. – sgeddes Jan 16 '13 at 15:01
I don't know until i give a weighed to my function as show in Adomar's answer. – Luigi Saggese Jan 16 '13 at 15:04

Order By Combined

2 Answers2