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 11 1587 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
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
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. 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
:-) 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
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
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. 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
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. 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
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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Dave Hopper |
last post by:
Hi
I am using the following SQL to retrieve a value in a list box using a
unique ID held in the list box call cntID. The list box is used on an
order form to list appointments that have been...
|
by: Christopher Weaver |
last post by:
I'm having the hardest time doing the simplest thing.
I have a DataGrid bound to a table in a master detail DataRelation within a
DataSet
relTaskActivities = new DataRelation("TaskActivities",...
|
by: Ian Williamson |
last post by:
Greetings,
My company has an ASP.NET based enterprise product that is undergoing some
changes and I need some community input to help solve a problem.
In the current implementation, any given...
|
by: jimmyfo |
last post by:
Hi, I recently wrote an ASP.Net web application in VS2005 and published
(using VS2005 Publish feature) it to a relatively clean machine with
ASP.Net 2.0 and MDAC 2.8 installed on it. However, when...
|
by: jimmyfo |
last post by:
Hi, I recently wrote an ASP.Net web application in VS2005 and published
(using VS2005 Publish feature) it to a relatively clean machine with
ASP.Net 2.0 and MDAC 2.8 installed on it. However, when...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...
| |