检索最新的条目？

编程入门行业动态更新时间:2024-10-23 22:35:06

本文介绍了检索最新的条目？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！问题描述

在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 写道：在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.