检索最新的条目？ [英] Retrieving most current entry?
问题描述
在AIX上的DB2上，我试图根据其EffectiveDate检索每个
唯一代码的最新ID。例如，在下面的数据
中，我想检索ID BE0191026，因为它具有最新的
EffectiveDate，1/1/2001，用于代码的记录PG2SS0。可以
有人建议适当的方法来获取所需的SQL查询吗？
谢谢，Jim D.
代码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
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
推荐答案
jd ****** @ yahoo.com 写道：
在AIX上的DB2上，我'' m试图根据其Effe检索每个
唯一代码的最新ID ctiveDate。例如，在下面的数据中，我想要检索ID BE0191026，因为它具有最新的有效日期，即1/1/2001，用于代码PG2SS0的记录。可以
任何人建议适当的方法来进行所需的SQL查询吗？
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?
EffectiveDate是字符串还是SQL-Date？当SQL Dateselect * from
table where CODE =''PGSS0''order by EffectiveDate desc fetch first row
只优化1行
如果EffectiveDate是一个字符串，你需要一个函数来构建一个yyyymmdd
字符串进行排序。
Bernd
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
阅读ROW_NUMBER（）OVER（）OLAP表达式。
...或者来到我在海牙IM Tech Conf的演讲。 5月15日至19日
干杯
Serge
-
Serge Rielau
DB2解决方案开发
IBM多伦多实验室
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
感谢您的建议。似乎这两种方法都可以工作但是我现在遇到的问题是适当地循环。我基本上需要处理每个具有唯一代码的组。对于
示例，我希望从我的示例数据中检索6行，每个唯一的代码一行
行。一旦我想到如何做到这一点，我看到FETCH
FIRST或ROW_NUMBER（（）OVER（）都工作。
Serge，我很感谢邀请，但是作为IBM
项目的IBM承包商，我怀疑IBM和IBM的客户都不会感激我在系统测试过程中起飞。当然，如果我在目前的驼峰上没有得到
，那么将会有更少的东西要测试.8-）
问候，Jim D.
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.
这篇关于检索最新的条目？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
