How to get column ordered by average of difference of a field from some table in SQL

I have a table like:

I have to order the rows in function of the average of the difference of v field.

For example:

all f1:

v1= 1;
v2 = 10;
v3 = 451;
average(f1) = ((abs(1-10))+(abs(10-451)))/3;
average(f2) = ....

so I have to have the columns ordered by this average in descendent mode.

Is possible to do with one SQL query? Can someone help me?

Answers


You don't have to calculate the difference between all values, you only need to know the smallest, the largest, and the number of values.

If you look at these values:

( abs(1-10) + abs(10-451) ) / 3

If you just always subtract the smaller from the larger, you don't need the abs:

( (10-1) + (451-10) ) / 3

The inner parentheses aren't needed, so you get:

(10 - 1 + 451 - 10) / 3

Here you can elliminate the inner value 10, as you have +10 and -10. You end up with just the largest and the smallest values:

(451 - 1) / 3

It doesn't matter how many middle values you have, they will always be elliminated by themselves, e.g. (b-a)+(c-b)+(d-c)+(e-d)+(f-e) = (f-a).

So, the SQL for this would be:

select name, (max(v) - min(v)) / count(*) as averageDiff
from TheTable
group by name
order by averageDiff desc

Note: I'm not sure what this average of differences is supposed to mean, but you are dividing the average differences with the number of items, but you might want to divide it with the number of differences instead, i.e one less than the number of items; (count(*) - 1).


Need Your Help

Is there a way to replace an already defined preprocessor identifier?

iphone objective-c c ios ipad

I have a library that has several options defined as this:

How to call JavaScript function from ajax return

php javascript ajax json function

I have a $.POST call that return the name of a function that need to be ran but it wont execute the function and I don't know why.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.