如何为DB2中的每个列类型获取单个最接近的值

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.

结果我获得了大约12个记录(行)。

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?

如何为每个TYPE_COLUMN获得最接近DATE_EFFECTIVE_COLUMN的结果?在这种情况下,如何获得最接近生效日期的每种类型的三条记录?

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.

更新:如果我不得不只使用单一类型,我可以使用TOP,但此时我有三个,并且对于每个我需要获得最接近的时间结果。

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

希望我说清楚,如果您需要更多信息,请告诉我。

2 个解决方案

#1

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

如果我理解正确,您可以使用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.

如果你想要每种类型三个记录,只需使用seqnum <= 3。

#2

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):

我喜欢ROW_NUMBER()。您希望按TYPE分区,它将为每种类型开始行计数,然后按DATE_EFFECTIVE desc排序,并仅采用最高日期(第一行):

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

#1