SQL: I want to compare three rows of data based on a criterion and identify the one with the lowest value

user_16512286 0
Viewed 45

I have an inspection table that shows the serial number, the inspection (Characteristic), and the test result (TestValue). In the sample table, I show five inspections for each serial number, but for a set of inspections I need to find the one that has the lowest value. I actually need the absolute value of 90 minus the TESTVALUE and identify the lowest decimal result. I did not include that calculation in my sample table. I have tried several different methods, but they have ultimately failed due to the fact that they do not take into account multiple criteria but only needing this calculation on a specific set.

This data goes into a report and I tried doing this in Report Builder and did not fair any better.

TABLE: Inspections COLUMNS: Serial, Characteristic, TestValue

Sample Table

reporting-services sql-server
2 Answers

In the future, please provide sample data as text, not an image.

Please note the left(Characteristic,5) ... this may be an issue down the line

Perhaps this will help

Select * 
      ,MinValue =  TestValue 
                  * case when sum(1) over (partition by Serial,left(Characteristic,5)) > 1 then 1 end
                  * case when TestValue = min(TestValue) over (partition by Serial,left(Characteristic,5)) then 1 end
 From YourTable

Results

enter image description here

user_15700000

Usually when looking for the lowest value within a group I would suggest using a ROW_NUMBER function to identify the rows you are interested in. However, in this case, a subquery seems like a good alternative.

The subquery would be something like this:

select serial, min(testvalue) as mintestvalue
from table
where characteristic like 'Angle%'
group by serial

Now you have the appropriate test values you wanted for each serial number. Left-join this subquery to the dataset on serial number to have the mintestvalue column included in your dataset.

user_20337170
Related