选择表中行的第二行 [英] Select the 2nd of a sequence of rows in a table
问题描述
你好,
DB2 for iSeries - V5R2
我需要从表中的每组SEDOL行中提取第二行。
如果只存在一个SEDOL行,则选择那一行。 />
例如:
SEDOL状态
A
10001 A
10001 B
10001 C
10001 D
10050 A
10050 B
10051 A
10100 A
10100 B
所以,我需要提取
10000 A
10001 B
10050 B
10051 A
10100 B
建议我如何做到这一点。
谢谢
Glenn
GL ************@quattroconsulting.co.uk 写道:
您好,
DB2 for iSeries - V5R2
我有一个非唯一索引的表,列名为SEDOL。
我需要从
表。如果只存在一个SEDOL行,那么选择那一行。
例如:
SEDOL状态
10000 A
10001 A
10001 B
10001 C
10001 D
10050 A
10050 B
10051 A
10100 A
10100 B
所以,我需要提取
10000 A
10001 B
10050 B
10051 A
10100 B
关于我如何做到这一点的建议值得欢迎。
谢谢
格伦
嗯。 ..这样的事可能吗?可能不是最好的解决方案,但
似乎有效(顺便说一句,我认为这张桌子叫做SEDOLTABLE):
WITH
>
SEDOL1 AS(
SELECT SEDOL,COUNT(*)作为ROWCOUNT
来自SEDOLTABLE
GROUP by SEDOL
),
SEDOL2 AS(
SELECT SEDOL,STATUS,ROW_NUMBER()超过(由SEDOL划分
按状态排序)作为ROWNUM
来自SEDOLTABLE
),
SEDOL3 AS(
SELECT B.SEDOL,B.STATUS, B.ROWNUM,A.ROWCOUNT
来自SEDOL1内部联接SEDOL2 B ON A.SEDOL = B.SEDOL
)
SELECT SEDOL,STATUS
来自SEDOL3
WHERE
(ROWCOUNT = 1)或
(ROWCOUNT> 1和ROWNUM = 2);
HTH,
戴夫。
-
< laughing>
你的问题没有答案,但是:
有趣的是,我确切地知道什么是SEDOL是..
我花在这些和CUSIP上的时间......
干杯
aj
gl************@quattroconsulting.co.uk 写道:你好,
DB2 for iSeries - V5R
我有一个表有一个非唯一索引,列名SEDOL。
我需要从表格中的每组SEDOL行中提取第二行。
如果只存在一个SEDOL行,则选择该行。
>作为一个例子:
SEDOL状态
10000 A
10001 A
10001 B
10001 C
10001 D
10050 A
10050 B
10000 A
10100 B
所以,我需要提取
> 10000 A
10001 B
10050 B
10051 A
10100 B
关于如何做到这一点的建议将受到欢迎。
谢谢
格伦
戴夫,
我知道会有办法。仍然没有找到SQL无法处理的查询。
非常感谢。
Glenn
Hello,
DB2 for iSeries - V5R2
I have a table with a non-unique index, column name SEDOL.
I need to extract the 2nd row from each set of SEDOL rows in the table.
If only one SEDOL row exists then select that one row.
As an example:
SEDOL STATUS
10000 A
10001 A
10001 B
10001 C
10001 D
10050 A
10050 B
10051 A
10100 A
10100 B
So, I need to extract
10000 A
10001 B
10050 B
10051 A
10100 B
Suggestions on how I do this would be welcome.
Thanks
Glenn
解决方案gl************@quattroconsulting.co.uk wrote:
Hello,
DB2 for iSeries - V5R2
I have a table with a non-unique index, column name SEDOL.
I need to extract the 2nd row from each set of SEDOL rows in the
table. If only one SEDOL row exists then select that one row.
As an example:
SEDOL STATUS
10000 A
10001 A
10001 B
10001 C
10001 D
10050 A
10050 B
10051 A
10100 A
10100 B
So, I need to extract
10000 A
10001 B
10050 B
10051 A
10100 B
Suggestions on how I do this would be welcome.
Thanks
Glenn
Hmmm... Something like this maybe? Probably not the best solution, but
it seems to work (BTW, I''ve assumed the table is called SEDOLTABLE):
WITH
SEDOL1 AS (
SELECT SEDOL, COUNT(*) AS ROWCOUNT
FROM SEDOLTABLE
GROUP BY SEDOL
),
SEDOL2 AS (
SELECT SEDOL, STATUS, ROW_NUMBER() OVER (PARTITION BY SEDOL
ORDER BY STATUS) AS ROWNUM
FROM SEDOLTABLE
),
SEDOL3 AS (
SELECT B.SEDOL, B.STATUS, B.ROWNUM, A.ROWCOUNT
FROM SEDOL1 A INNER JOIN SEDOL2 B ON A.SEDOL = B.SEDOL
)
SELECT SEDOL, STATUS
FROM SEDOL3
WHERE
(ROWCOUNT = 1) OR
(ROWCOUNT > 1 AND ROWNUM = 2);
HTH,
Dave.
--
<laughing>
No answer for your question, but:
Interestingly enough, I know exactly what a SEDOL is..
The hours that I have spent with those and CUSIPs...
Cheers
aj
gl************@quattroconsulting.co.uk wrote:Hello,
DB2 for iSeries - V5R2
I have a table with a non-unique index, column name SEDOL.
I need to extract the 2nd row from each set of SEDOL rows in the table.
If only one SEDOL row exists then select that one row.
As an example:
SEDOL STATUS
10000 A
10001 A
10001 B
10001 C
10001 D
10050 A
10050 B
10051 A
10100 A
10100 B
So, I need to extract
10000 A
10001 B
10050 B
10051 A
10100 B
Suggestions on how I do this would be welcome.
Thanks
Glenn
Dave,
I knew there''d be a way. Still not found a query that SQL can''t handle.
Thanks for this.
Glenn
这篇关于选择表中行的第二行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!