Select MIN decimal value-CodePudding

I have a scenario where I have duplicate data in a file - where the 'unique' identifiers equal the same.

I am trying to identify which of the decimal values in the 'Outcome1' field is the MIN value, which will then give me results which are not duplicated.

I have tried to use MIN, but it seems to not work as I have to group by the 'Outcome1' field.

The current table looks like this:

ID	Outcome1	Outcome2
1	186.5098	133.8825
1	186.5093	133.8820

The expected result is as follows:

ID	Outcome1	Outcome2
1	186.5093	133.8820

I would really appreciate any assistance.

CodePudding user response：

If you only want to consider Outcome1 you can use row_number

with outcome as (
    select *,
        Row_Number() over(partition by id order by Outcome1 ) rn
    from t
)
select Id, Outcome1, Outcome2
from outcome
where rn=1

CodePudding user response：

Assuming you want the lowest value from both Outcome1 and Outcome2 you would do this with a basic MIN.

select ID
    , Outcome1 = MIN(Outcome1)
    , Outcome2 = MIN(Outcome2)
from YourTable
group by ID

CodePudding user response：

use Row_number() in a partial query and then get the lines that take the number 1

select * from (
   SELECT 
   [id]
  ,[Outcome1]
  ,[Outcome2]
  , ROW_NUMBER() over( partition by id order by Outcome1) As RowN
  FROM [Test].[dbo].[Table_4]
 ) tt
 where tt.RowN=1