Help with MAX Function

[ks01]

Hello.

I hope I can explain my question properly, please let me know if I have left anything out. To begin, here is the table I’m working with (DB2 on OS/390):

[tt] Column Name ColNo Datatyp Length
----1----V----2----V----3----V----4----V
CONFIG_VERS_ID 1 CHAR 8
CORE_TARGT_INST_DT 2 DATE 4
APPLY_INIT_TS 3 TIMESTMP 10
APPLY_INIT_ID 4 CHAR 8
CORE_VERS_NUM 5 INTEGER 4
CORE_VERS_NUM2 6 INTEGER 4
CORE_VERS_NUM3 7 INTEGER 4
CORE_VERS_NUM4 8 INTEGER 4
CORE_VERS_NUM5 9 INTEGER 4
CORE_VERS_NUM6 10 INTEGER 4
CORE_VERS_NUM7 11 INTEGER 4
CORE_VERS_NUM8 12 INTEGER 4
CORE_VERS_NUM9 13 INTEGER 4
CORE_VERS_NUM10 14 INTEGER 4[/tt]

The table is used a log table. Each time we submit a process, we can enter up to 10 variables (CORE_VERS_NUMX) from one application which is assigned to one variable from another (CONFIG_VERS_ID).

The objective here is to find the most recent time the variable (CONFIG_VERS_ID) was used. There are seven variables in the CONFIG_VERS_ID I am looking to query on. Here is my query:

  SELECT CONFIG_VERS_ID,
         CORE_TARGT_INST_DT,
         APPLY_INIT_TS,
         APPLY_INIT_ID,
         CORE_VERS_NUM,
         CORE_VERS_NUM2,
         CORE_VERS_NUM3,
         CORE_VERS_NUM4,
         CORE_VERS_NUM5,
         CORE_VERS_NUM6,
         CORE_VERS_NUM7,
         CORE_VERS_NUM8,
         CORE_VERS_NUM9,
         CORE_VERS_NUM10
    FROM DBA2.VB2_VSS_PTS_CNTL
   WHERE CONFIG_VERS_ID IN ('CORESRE1',
                            'CORESRE2',
                            'CORESRE3',
                            'CORESRE4',
                            'CORESRE5',
                            'CORESRE6',
                            'CORESRE7')
ORDER BY CONFIG_VERS_ID,
         APPLY_INIT_TS DESC;

Of course, this is my result set:[tt]

CORE
CONFIG TARGT APPLY APPLY CORE CORE
VERS INST INIT INIT VERS VERS
ID DT TS ID NUM NUM2
--------+----------+----------+--------+-----------+-----
CORESRE1 2004-05-18 2004-05-11 #TIAL 8878 0
CORESRE1 2004-05-16 2004-05-07 #TIAL 8841 8843
CORESRE1 2004-05-04 2004-04-29 #TIAL 8814 0
CORESRE1 2004-05-02 2004-04-21 #TIAL 8744 8757
CORESRE1 2004-04-25 2004-04-20 #TIAL 8700 0
CORESRE1 2004-04-25 2004-04-12 #EDGO 8536 0
CORESRE1 2004-04-14 2004-04-05 #TIAL 8619 8621
CORESRE1 2004-04-07 2004-04-02 #EDGO 8614 0
CORESRE1 2004-03-21 2004-03-10 #HENL 8470 8471
CORESRE1 2004-02-22 2004-02-14 #HENL 8321 8327
CORESRE1 2004-01-25 2004-01-15 #TIAL 8189 8194
CORESRE1 2004-01-18 2004-01-07 #TIAL 8129 8130
CORESRE1 2004-01-04 2003-12-22 #HENL 8066 8067[/tt]

As you can see, my query returns all instances of variable one (CONFIG_VERS_ID). I think I need to use the MAX function, but I’m not familiar how. I only want to see one instance, the most recent instead (APPLY_INIT_TS is a timestamp field but I've converted it to a date so I could get more data in the example) of each of the CORESREX numbers, not all of them.

Can someone assist?

Thank you in advance ...

 

[PHV]

Something like this ?
SELECT A.CONFIG_VERS_ID,
A.CORE_TARGT_INST_DT,
A.APPLY_INIT_TS,
A.APPLY_INIT_ID,
A.CORE_VERS_NUM,
A.CORE_VERS_NUM2,
A.CORE_VERS_NUM3,
A.CORE_VERS_NUM4,
A.CORE_VERS_NUM5,
A.CORE_VERS_NUM6,
A.CORE_VERS_NUM7,
A.CORE_VERS_NUM8,
A.CORE_VERS_NUM9,
A.CORE_VERS_NUM10
FROM DBA2.VB2_VSS_PTS_CNTL A INNER JOIN (
SELECT CONFIG_VERS_ID, Max(APPLY_INIT_TS) LastApply
FROM DBA2.VB2_VSS_PTS_CNTL GROUP BY CONFIG_VERS_ID
) B ON A.CONFIG_VERS_ID=B.CONFIG_VERS_ID
AND A.APPLY_INIT_TS=B.LastApply
WHERE CONFIG_VERS_ID IN ('CORESRE1',
'CORESRE2',
'CORESRE3',
'CORESRE4',
'CORESRE5',
'CORESRE6',
'CORESRE7')
ORDER BY A.CONFIG_VERS_ID;


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886