CODEWITHSUNDEEP
sqlsql-servert-sql
Rohit Raj
Rohit Raj

Reputation: 133

Find Latest Record With Specific Values

I want to find the row with the latest Date in the group. However, if that row has some specific value in specific column, then we must eliminate the row completely For Ex in the following table:

 ID   FK_ID    DateTiME     Value
  1     1     2017-06-01    a
  2     1     2017-06-02    a
  3     2     2017-06-04    b
  4     2     2017-06-03    a 
  5     2     2017-06-01    b
  6     3     2017-01-01    c
  7     3     2017-01-01    a

Here I want the output as 

FK_ID   DateTime 
  1    2017-06-02
  3    2017-01-01

I want to group on FK_ID and then want the Max DateTime Value from the Group. However, If the selected Row of the Group has Value as b then we should skip the row entirely and don't want it in the output.

For Example, in the above table(1), the row with Max DateTime for FK_ID 2 is 

  3     2     2017-06-04    b

However, as the value for this row is b, we don't want it in the output.

Upvotes: 2

Views: 63

Answers (2)

Pரதீப்
Pரதீப்

Reputation: 93694

Here is one way to this

select top 1 with ties * 
from yourtable a
where not exists(select 1  from yourtable b where a.FK_ID =b.FK_ID and b.Value = 'b') 
order by row_number()over(partition by FK_ID order by [DateTiME] desc)

Upvotes: 2

JohnHC
JohnHC

Reputation: 11195

Use row_number

With CTE as
(
select t1.*, row_number() over (partition by FK_ID order by datetime desc) rn
from MyTable t1
)
select *
from CTE
where rn = 1
and value <> 'b'

Upvotes: 3

Related Questions