How to get single closest value for each column type in DB2

Question

I have this query:

SELECT * FROM TABLE1 WHERE KEY_COLUMN='NJCRF' AND TYPE_COLUMN IN ('SCORE1', 'SCORE2', 'SCORE3') AND DATE_EFFECTIVE_COLUMN<='2016-09-17'

I get about 12 record(rows) as result.

How to get result closest to DATE_EFFECTIVE_COLUMN for each TYPE_COLUMN? In this case, how to get three records, for each type, that are closest to effective date?

UPDATE: I could use TOP if I had to go over only single type, but I have three at this moment and for each of them I need to get closest time result.

Hope I made it clear, let me know if you need more info.

Possible duplicate of [Find closest date in SQL Server](https://stackoverflow.com/questions/14023145/find-closest-date-in-sql-server) — Jacob H, May 30 '17 at 19:56

score 1 · Accepted Answer · answered May 30 '17 at 20:08

If I understand correctly, you can use ROW_NUMBER():

SELECT t.*
FROM (SELECT t.*,
             ROW_NUMBER() OVER (PARTITION BY TYPE_COLUMN ORDER BY DATE_EFFECTIVE_COLUMN DESC) as seqnum
      FROM TABLE1 t
      WHERE KEY_COLUMN = 'NJCRF' AND
            TYPE_COLUMN IN ('SCORE1', 'SCORE2', 'SCORE3') AND
            DATE_EFFECTIVE_COLUMN <= '2016-09-17'
     ) t
WHERE seqnum = 1;

If you want three records per type, just use seqnum <= 3.

score 1 · Answer 2 · answered May 30 '17 at 20:08

I like ROW_NUMBER() for this. You want to partition by TYPE, which will start the row count over for each type, then order by DATE_EFFECTIVE desc, and take only the highest date (the first row):

SELECT *
FROM (
    SELECT *, 
       ROW_NUMBER() over (PARTITION BY TYPE_COLUMN ORDER BY DATE_EFFECTIVE_COLUMN desc) RN
    FROM TABLE1 
    WHERE KEY_COLUMN = 'NJCRF' 
    AND TYPE_COLUMN IN ('SCORE1', 'SCORE2', 'SCORE3') 
    AND DATE_EFFECTIVE_COLUMN <= '2016-09-17'
     ) A
WHERE RN = 1

How to get single closest value for each column type in DB2

2 Answers2