# Select lowest ID and calculate deviation from the lowest

I'm trying to create a view. I have some data in my SQL Server database, and I want to order them by their `mark1` and `mark2`. Now when I sort them I want to select the `mark1` who has the lowest `mark2` and calculate what's the deviation from other `mark1`s. The table is looking right now like this, but without deviation.

``````|   mark1    |    mark2    |      mass    ||    deviation |
|:-----------|------------:|:------------:||:------------:|
| 0001       |      10000  |     10,0     ||    min0001   |
| 0001       |      15000  |     12,0     ||    +20%      |
| 0002       |      11200  |     18,0     ||    min0002   |
| 0002       |      13000  |     35,5     ||    +97.2%    |
| 0002       |      14565  |     85,5     ||    375%      |
| 0003       |      15004  |     15,4     ||    min0003   |
| 0003       |      21250  |     17,4     ||    +12.9%    |
| 0003       |      60764  |     16,4     ||    6.49%     |
``````

And now, as you see it's ordered by `mark1` and `mark2`. Now, my problem is the deviation column, I need it to take `mark1`, Let's say `0001` (his `mark2` is `10000`) and find the lowest mass and find the deviation from other `0001` (`15000`). As you can see the in the table.

• Which version of SQL Server are you using? The newer versions have some functions that would make this much easier. May 15, 2015 at 12:54
• SQL 2014 version...I hope it helps... May 15, 2015 at 12:58
• Do you want to compare with the lowest mass or with the mass of the lowest mark2? May 17, 2015 at 11:45

Returns the first value in an ordered set of values in SQL Server 2014.

Partition on `mark1` and order by `mark2`.

Something like this should do it for you:

``````select T.*,
100 * (T.mass - T.FirstMass) / T.FirstMass as Deviation
from (
select T.*,
first_value(T.mass) over(partition by T.mark1 order by T.mark2) as FirstMass
from dbo.YourTable as T
) as T;
``````

SQL Fiddle

• Mikael beat me to it :) May 15, 2015 at 13:23
• Yes, realized it and removed my comment ;) The data and wording suggest your solution (except for that "lowest mass", they need to clarify.) May 17, 2015 at 11:47
• @ypercube what comment :) May 17, 2015 at 11:48

I think this query will give the results:

``````SELECT
*,
(t.mass / MIN(mass) OVER (PARTITION BY mark1) - 1) * 100 AS devValue,
CASE
WHEN t.mass = MIN(mass) OVER (PARTITION BY mark1) THEN 'min' + mark1
ELSE
CASE
WHEN t.mass > MIN(mass) OVER (PARTITION BY mark1) THEN '+'
ELSE '-'
END +
CONVERT(varchar, CONVERT(DECIMAL(10,2), (t.mass / MIN(mass) OVER (PARTITION BY mark1) - 1) * 100)) + '%'
END AS deviation
FROM
t
ORDER BY
t.mark1, t.mark2
``````