Retrieving most current entry?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

jd_12345@yahoo.com - 17 Apr 2006 20:37 GMT

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

Reply to this Message

Bernd Hohmann - 17 Apr 2006 21:25 GMT

> 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

Reply to this Message

Serge Rielau - 17 Apr 2006 21:50 GMT

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

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

jd_12345@yahoo.com - 18 Apr 2006 01:43 GMT

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.

Reply to this Message

Serge Rielau - 18 Apr 2006 03:27 GMT

> 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

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

mirof007 - 18 Apr 2006 03:42 GMT

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

Reply to this Message

Serge Rielau - 18 Apr 2006 03:55 GMT

> :-) LOL

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

Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

jd_12345@yahoo.com - 19 Apr 2006 00:31 GMT

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.

Reply to this Message

Serge Rielau - 19 Apr 2006 01:07 GMT

> Thanks a million to both of you. My final code that worked was:
> SELECT DISTINCT CVGE_BNFT_PLN_ID
[quoted text clipped - 14 lines]
> 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*

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

jd_12345@yahoo.com - 19 Apr 2006 04:08 GMT

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.

Reply to this Message

Serge Rielau - 19 Apr 2006 14:55 GMT

> 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

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

jd_12345@yahoo.com - 19 Apr 2006 19:04 GMT

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.

Reply to this Message