CODEWITHSUNDEEP
sqldb2
MetaStack
MetaStack

Reputation: 3696

sql get latest row before date for value

I know this question has been answered before but maybe it's because I'm on DB2 or something but I can't find a solution that works for me.

I need to select only one (the most recent) of every record according to duplicate KEY column's value.

Here's the data:

KEY   TIME_STAMP                  STATUS
A     1992-06-30-00.00.00.100000  O
A     1992-02-03-00.00.00.100000  O
A     1991-12-20-00.00.00.100000  O
B     1992-11-19-00.00.00.100000  P
B     1992-11-17-00.00.00.100000  O
B     1992-10-08-00.00.00.100000  O
B     1992-09-24-00.00.00.100000  O
B     1992-09-18-00.00.00.100000  O
C     1984-06-21-00.00.00.100000  O
C     1984-06-05-00.00.00.100000  U
D     1986-04-22-00.00.00.100000  O
D     1986-04-14-00.00.00.100000  O
D     1986-03-16-00.00.00.100000  O
D     1986-03-07-00.00.00.100000  R
E     1985-04-03-00.00.00.100000  O
F     1987-08-14-00.00.00.100000  M
F     1987-07-17-00.00.00.100000  A

And I need it to be:

KEY   TIME_STAMP                  STATUS
A     1992-06-30-00.00.00.100000  O
B     1992-11-19-00.00.00.100000  P
C     1984-06-21-00.00.00.100000  O
D     1986-04-22-00.00.00.100000  O
E     1985-04-03-00.00.00.100000  O
F     1987-08-14-00.00.00.100000  M

Notice only the most recent KEY of it's type remain.

So here's the code I have so far... that actually creates the top data from a huge table:

SELECT KEY, TIME_STAMP, STATUS
FROM HIST
WHERE STATUS IN ('P','O','U','R','M','A')
    AND TIME_STAMP < '1993-01-01-00.00.00.100000'
ORDER BY KEY, TIME_STAMP DESC

I don't know what to do from here to trim it down further.

Upvotes: 0

Views: 100

Answers (2)

Esperento57
Esperento57

Reputation: 17462

Other method, but Jorge method is better

with perimeter as (
    select KEY, TIME_STAMP, STATUS
    FROM HIST
    WHERE STATUS IN ('P','O','U','R','M','A') AND TIME_STAMP < '1993-01-01-00.00.00.100000'
)
select distinct f3.* from perimeter f1
inner join lateral
(
  select * from perimeter f2
  where f1.key=f2.key
  order by f2.TIME_STAMP desc 
  fetch first rows only                 
) f3 on 1=1

Upvotes: 1

Jorge Campos
Jorge Campos

Reputation: 23361

You can use a Window Function:

select *
  from (
   select KEY,
          TIME_STAMP,
          STATUS,
          row_number() over (partition by KEY order by TIME_STAMP desc) as rn
     FROM HIST
    WHERE STATUS IN ('P','O','U','R','M','A')
      AND TIME_STAMP < '1993-01-01-00.00.00.100000'
       )
  WHERE rn = 1;

Upvotes: 2

Related Questions