Take latest row if condition met else previous row

user_8669616 0
Viewed 60

I have a table say

+--------+-----+-------------+-----------+
| ID     | REV | Description | curr      |
+--------+-----+-------------+-----------+
| 211-32 | 001 | Screw       | READY     |
| 211-32 | 002 | Screw_2     | NULL      |
| 212-41 | 001 | bolt        | READY     |
| 212-41 | 002 | bolt_v2     | READY     |
| 423-98 | 001 | Nut         | WITHDRAWN |
| 423-98 | 002 | Nut_2       | NULL      |
+--------+-----+-------------+-----------+

I want to take the ID with latest revision from this. But if the curr is "NULL" then i have to take the previous row and if the previous curr is WITHDRAWN , I don't need that ID itself. So my expected output is like below

+--------+-----+-------------+-------+
| ID     | REV | Description | curr  |
+--------+-----+-------------+-------+
| 211-32 | 001 | Screw       | READY |
+--------+-----+-------------+-------+
| 212-41 | 002 | BOLT_2      | READY |
+--------+-----+-------------+-------+

I have tried the below query using temp table but it is not giving all rows.

select *,dense_rank() over (partition by id order by rev desc) as DR 
into #material_DN
from material

select * from #material_DN where DR = case when curr='NULL' then 2 else 1 end

Check the fiddle here

sql-server-2012 sql-server
4 Answers

Try this ...

select m.* from (
   select id, max(rev) max_rev
   from material
   where ISNULL(curr, '') <> 'NULL'
   group by id
) s
join material m ON
   m.id = s.id and 
   m.rev = s.max_rev
where
   m.curr <> 'WITHDRAWN'

You can change ISNULL(curr, '') <> 'NULL' to simply curr <> 'NULL' .. I was just trying to be safe with your sample data types.

user_12239390

Try this:

WITH DataSource AS
(
    SELECT *
          ,DENSE_RANK() OVER (PARTITION BY ID ORDER BY REV DESC) AS [row_id]
          ,LEAD(curr) OVER (PARTITION BY ID ORDER BY REV ASC) AS [next]
    FROM material
)
SELECT id, rev, description, curr
FROM DataSource
WHERE ([row_id] = 1 AND [curr] = 'READY')
    OR ([row_id] = 2 and [next] = 'NULL' and [curr] <> 'WITHDRAWN')
ORDER BY ID
        ,[Rev]
user_10803540

Here's a simpler version using window funcions ...

select * from (
    select *, max(case when curr <> 'NULL' then rev end) over (partition by id) max_rev
    from material
) s where
   rev = max_rev and
   curr <> 'WITHDRAWN';
user_12239390

You can try the following query using the reference.

;with cte as (
SELECT ID
, Rev
, Description
, curr = MAX(curr) OVER (PARTITION BY c)
, Row_Number() Over(Partition By Id Order by id desc) as SrNo
FROM
(
    SELECT ID, Rev, Description, curr
        ,c = count(curr) OVER (ORDER BY id)
    FROM material
) a
)
Select ID
, Rev
, Description
, curr
from cte where SrNo = 2 order BY id

Live Demo db<>fiddle

user_105325000
Related