Retrieving most current entry?

jd_12345

On DB2 on AIX, I'm attempting to retrieve the most recent ID for each
unique Code based on its EffectiveDate. For example, in the data
below, I want to retrieve ID BE0191026 as it has the most recent
EffectiveDate, 1/1/2001, for the records with the Code PG2SS0. Can
anyone suggest an appropriate approach for the required SQL query?
Thanks, Jim D.

Code ID EffectiveDate
103631 BE010662E 8/1/2003
103631 BE010662E 8/1/2003
10004X BE0106Y50 8/1/2003
QJD BE0189010 9/27/1999
QJD BE0189010 7/15/2002
PG2SS0 BE0191002 12/1/1996
PG2SS0 BE0191002 5/1/2000
PG2SS0 BE0191002 12/1/1996
PG2SS0 BE0191002 5/1/2000
PG2SS0 BE0191026 1/1/2001
PG2SS0 BE0191008 12/1/1996
PG2SS0 BE0191008 5/1/2000
AR02 1/1/1992
P2F BE0116020 11/8/1995
P3F BE0116020 11/8/1995

Apr 17 '06 #1

Bernd Hohmann

jd******@yahoo.com wrote:

On DB2 on AIX, I'm attempting to retrieve the most recent ID for each
unique Code based on its EffectiveDate. For example, in the data
below, I want to retrieve ID BE0191026 as it has the most recent
EffectiveDate, 1/1/2001, for the records with the Code PG2SS0. Can
anyone suggest an appropriate approach for the required SQL query?

Is EffectiveDate a string or a SQL-Date? When SQL Date "select * from
table where CODE='PGSS0' order by EffectiveDate desc fetch first row
only optimize for 1 row"

If EffectiveDate is a string you need a function which builds a yyyymmdd
string for sorting.

Bernd

Apr 17 '06 #2

Serge Rielau

Read up on ROW_NUMBER() OVER() OLAP expression.
... or come to my presentation in The Hague at IM Tech Conf. May 15-19

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Apr 17 '06 #3

jd_12345

Thanks for the suggestions. It seems that both of these approaches
would work but the problem I'm having now is looping appropriately. I
basically need to process each group having a unique "Code". For
example, I would expect 6 rows to be retrieved from my sample data, one
row for each unique "Code". Once I figure how to do that I see FETCH
FIRST or ROW_NUMBER(() OVER() both working.

Serge, I appreciate the invitation but as an IBM contractor on an IBM
project, I suspect both IBM & IBM's client wouldn't appreciate me
taking off in the middle of system testing. Of course, if I don't get
over my current hump, there'll be one less thing to test. 8-)

Regards, Jim D.

Apr 18 '06 #4

Serge Rielau

jd******@yahoo.com wrote:

Serge, I appreciate the invitation but as an IBM contractor on an IBM
project, I suspect both IBM & IBM's client wouldn't appreciate me
taking off in the middle of system testing. Of course, if I don't get
over my current hump, there'll be one less thing to test. 8-)

Well it I gotta plug by talks where ever I can:
SELECT code, id, effectivedate
FROM (SELECT ROW_NUMBER() OVER(PARTITION BY code ORDER BY
effectivedate desc) as rn FROM T) AS X WHERE rn = 1;

Sametime or email when things get bumpy.
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Apr 18 '06 #5

mirof007

:-) LOL

Try the following (substitute your table name for T1):

select Code, EffectiveDate, ID from
(select T.*,
row_number() over (partition by Code order by EffectiveDate
desc) rn
from T1 as T) as TMP
where rn = 1;

Hope this helps,
Miro

Apr 18 '06 #6

Serge Rielau

mirof007 wrote:

:-) LOL

And don't be shy to ST or email.....
Your success pays our salary. :-)

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Apr 18 '06 #7

jd_12345

Thanks a million to both of you. My final code that worked was:
SELECT DISTINCT CVGE_BNFT_PLN_ID
FROM (SELECT ROW_NUMBER() OVER(PARTITION BY CVRGE_CDE_1
ORDER BY CVGE_PRCG_EFFV_DTE desc) as
rn,
CVRGE_CDE_1,
CVGE_BNFT_PLN_ID,
CVGE_PRCG_EFFV_DTE
FROM SCHEMA1.COVERAGE) AS X WHERE rn = 1;

I initially prefaced each object with the table name but that resulted
in errors as follows:
SQL0206N "COVERAGE.CVRGE_CDE_1" is not valid in the context where it
is used.
Once I eliminated the table names, the code worked fine.

Now I need to match the 7000 results against another table. I've
already found 7000 individual SELECTS won't perform too well.

Regards, Jim D.

Apr 18 '06 #8

Serge Rielau

jd******@yahoo.com wrote:

Thanks a million to both of you. My final code that worked was:
SELECT DISTINCT CVGE_BNFT_PLN_ID
FROM (SELECT ROW_NUMBER() OVER(PARTITION BY CVRGE_CDE_1
ORDER BY CVGE_PRCG_EFFV_DTE desc) as
rn,
CVRGE_CDE_1,
CVGE_BNFT_PLN_ID,
CVGE_PRCG_EFFV_DTE
FROM SCHEMA1.COVERAGE) AS X WHERE rn = 1;

I initially prefaced each object with the table name but that resulted
in errors as follows:
SQL0206N "COVERAGE.CVRGE_CDE_1" is not valid in the context where it
is used.
Once I eliminated the table names, the code worked fine.

Now I need to match the 7000 results against another table. I've
already found 7000 individual SELECTS won't perform too well.

Define match... the obvious answer is a join... but I presume you know
that..
BTW, I have a deja vu here.. did you ask the same question in Experts
Exchange? *just curious*

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Apr 19 '06 #9

jd_12345

Yes, I know I need a join. Now to get the code right.

No, I didn't post to EE. After 25 years on the Internet, hopefully I
know better then to cross-post.

Jim D.

Apr 19 '06 #10

Serge Rielau

jd******@yahoo.com wrote:

Yes, I know I need a join. Now to get the code right. SELECT .. FROM
(SELECT DISTINCT CVGE_BNFT_PLN_ID
FROM (SELECT ROW_NUMBER() OVER(PARTITION BY CVRGE_CDE_1
ORDER BY CVGE_PRCG_EFFV_DTE desc)
as rn,
CVRGE_CDE_1,
CVGE_BNFT_PLN_ID,
CVGE_PRCG_EFFV_DTE
FROM SCHEMA1.COVERAGE) AS X WHERE rn = 1) AS X
JOIN T ON X.CVRGE_CDE_1 = T.CVRGE_CDE_1;

No, I didn't post to EE. After 25 years on the Internet, hopefully I
know better then to cross-post.

Fascinating coincidence.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Apr 19 '06 #11

jd_12345

Perhaps I should have looked at your message earlier. After about 3
hours I came up with the solution below. I believe I tried your method
but the Command Editor kept saying it didn't recognize the reference
[UserID].X. I don't know why it picked up the UserID. I also tried
creating Views but I never got either syntax right. The following
seems to work:

SELECT DISTINCT TBL_NME, RD_TYP, STRT_DTE, END_DTE
FROM SCHEMA1.BENEFIT_TABLES
WHERE BNFT_PLN IN (SELECT DISTINCT CVGE_BNFT_PLN_ID
FROM (SELECT ROW_NUMBER() OVER(PARTITION BY CVRGE_CDE_1
ORDER BY CVGE_PRCG_EFFV_DTE desc) AS rn,
CVRGE_CDE_1,
CVGE_BNFT_PLN_ID,
CVGE_PRCG_EFFV_DTE
FROM SCHEMA1.COVERAGE) AS X WHERE rn = 1)

I think I'm over the stuff I'm unfamiliar with or had forgotten long
ago. Now I can concentrate on my area of expertise, WebSphere
DataStage TX.

Thanks again, Jim D.

Apr 19 '06 #12