Select the 2nd of a sequence of rows in a table

[glenn.robinson@quattroconsulting.co.uk]

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 Hughes]

Re: Select the 2nd of a sequence of rows in a table

glenn.robinson@ quattroconsulti ng.co.uk wrote:
[color=blue]
> 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[/color]

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.
--

[aj]

Re: Select the 2nd of a sequence of rows in a table

<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

glenn.robinson@ quattroconsulti ng.co.uk wrote:[color=blue]
> 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
>[/color]

[glenn.robinson@quattroconsulting.co.uk]

Re: Select the 2nd of a sequence of rows in a table

Dave,

I knew there'd be a way. Still not found a query that SQL can't handle.

Thanks for this.

Glenn

[Dave Hughes]

Re: Select the 2nd of a sequence of rows in a table

glenn.robinson@ quattroconsulti ng.co.uk wrote:
[color=blue]
> Dave,
>
> I knew there'd be a way. Still not found a query that SQL can't
> handle.
>
> Thanks for this.
>
> Glenn[/color]

Glenn - I've just re-read your initial post and noticed you mentioned
that you're running this on DB2 V5R2 ... I must admit I haven't taken
that into consideration in the query. I can't remember if common table
expressions (WITH) or OLAP functions (ROW_NUMBER) are available under
that version.

Anyway, give it a try and if it doesn't work I'll see if I can figure
out another method...


Cheers,

Dave.
--

[4.spam@mail.ru]

Re: Select the 2nd of a sequence of rows in a table

Hello.

If you don't afraid of java UDF mail me at
mark(dot)b(at)m ail(dot)ru
I can send you fairly small java class and sql for creation java UDF
that tries to emulate rownumber function in UDB DB2 for LUW.
With this function you will be able to do this:
-----
declare global temporary table session.t
(
sedol int, status char(1)
) with replace on commit preserve rows not logged;

insert into session.t values (10000, 'A');
insert into session.t values (10001, 'A');
insert into session.t values (10001, 'B');
insert into session.t values (10001, 'C');
insert into session.t values (10001, 'D');
insert into session.t values (10050, 'A');
insert into session.t values (10050, 'B');
insert into session.t values (10051, 'A');
insert into session.t values (10100, 'A');
insert into session.t values (10100, 'B');

-- we have to order rows befor enumerating them
declare global temporary table session.t2 as
(
select * from session.t order by sedol, status
) with data with replace on commit preserve rows not logged;

with t (sedol, status, rn) as
(
select sedol, status, dwh.rownumber(c har(sedol), 16) as rn
from session.t2
)
select t1.sedol, coalesce(t2.sta tus, t1.status) as status
from t t1
left join t t2 on t1.sedol=t2.sed ol and t2.rn=2
where t1.rn=1;
-----
output:
----
SEDOL STATUS
--------------- ------
10000 A
10001 B
10050 B
10051 A
10100 B

5 record(s) selected.
----

Sincerely,
Mark B.

[Knut Stolze]

Re: Select the 2nd of a sequence of rows in a table

Dave Hughes wrote:
[color=blue]
> glenn.robinson@ quattroconsulti ng.co.uk wrote:
>[color=green]
>> Dave,
>>
>> I knew there'd be a way. Still not found a query that SQL can't
>> handle.
>>
>> Thanks for this.
>>
>> Glenn[/color]
>
> Glenn - I've just re-read your initial post and noticed you mentioned
> that you're running this on DB2 V5R2 ... I must admit I haven't taken
> that into consideration in the query. I can't remember if common table
> expressions (WITH) or OLAP functions (ROW_NUMBER) are available under
> that version.
>
> Anyway, give it a try and if it doesn't work I'll see if I can figure
> out another method...[/color]

An alternative could be this:

SELECT ...
FROM t AS t1
WHERE status = ( SELECT MAX(status)
FROM t AS t2
WHERE status < ( SELECT MAX(status)
FROM t AS t3
WHERE t3.sedol = t1.sedol ) AND
t2.sedol = t1.sedol )

If you wont to get the 3rd, 4th and so on, things will become more
complicated. Using the ROW_NUMBER/RANK function would be a better choice
then.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Knut Stolze]

Re: Select the 2nd of a sequence of rows in a table

Knut Stolze wrote:
[color=blue]
> Dave Hughes wrote:
>[color=green]
>> glenn.robinson@ quattroconsulti ng.co.uk wrote:
>>[color=darkred]
>>> Dave,
>>>
>>> I knew there'd be a way. Still not found a query that SQL can't
>>> handle.
>>>
>>> Thanks for this.
>>>
>>> Glenn[/color]
>>
>> Glenn - I've just re-read your initial post and noticed you mentioned
>> that you're running this on DB2 V5R2 ... I must admit I haven't taken
>> that into consideration in the query. I can't remember if common table
>> expressions (WITH) or OLAP functions (ROW_NUMBER) are available under
>> that version.
>>
>> Anyway, give it a try and if it doesn't work I'll see if I can figure
>> out another method...[/color]
>
> An alternative could be this:
>
> SELECT ...
> FROM t AS t1
> WHERE status = ( SELECT MAX(status)
> FROM t AS t2
> WHERE status < ( SELECT MAX(status)
> FROM t AS t3
> WHERE t3.sedol = t1.sedol ) AND
> t2.sedol = t1.sedol )[/color]

I forgot the case when there is only one row:

UNION
SELECT ...
FROM t AS t4
WHERE 1 = ( SELECT COUNT(*)
FROM t AS t5
WHERE t4.sedol = t5.sedol )

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Chris]

Re: Select the 2nd of a sequence of rows in a table

Or, with just OLAP (no with):

SELECT SEDOL, STATUS
FROM (SELECT SEDOL, STATUS,
ROW_NUMBER() OVER (PARTITION BY SEDOL ORDER BY STATUS) AS
ROWNUM,
COUNT(*) OVER (PARTITION BY SEDOL) AS ROWCOUNT ) X
WHERE (ROWCOUNT < 3 AND ROWNUM = ROWCOUNT)
OR (ROWCOUNT > 2 AND ROWCOUNT = 2)

[--CELKO--]

Re: Select the 2nd of a sequence of rows in a table

Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files; there is no sequential access or
ordering in an RDBMS, so "first", "next" and "last" are totally
meaningless. If you want an ordering, then you need to have a column
that defines that ordering. You must use an ORDER BY clause on a
cursor or in an OVER() clause.

Next, please the good manners to post DDL for people who are doing your
job or homework for free. Here is an answer in pure SQL-92, without
SQL-99 OLAP features.

CREATE TABLE Foobar
(sedol INTEGER NOT NULL, -- industry standard term??
foobar_status CHAR(1) NOT NULL,
PRIMARY KEY (sedol, foobar_status)) ;

INSERT INTO Foobar VALUES (10000, 'A');
INSERT INTO Foobar VALUES (10001, 'A');
INSERT INTO Foobar VALUES (10001, 'B');
INSERT INTO Foobar VALUES (10001, 'C');
INSERT INTO Foobar VALUES (10001, 'D');
INSERT INTO Foobar VALUES (10050, 'A');
INSERT INTO Foobar VALUES (10050, 'B');
INSERT INTO Foobar VALUES (10051, 'A');
INSERT INTO Foobar VALUES (10100, 'A');
INSERT INTO Foobar VALUES (10100, 'B');
*/

SELECT F0.sedol,
CASE WHEN MIN(F0.foobar_s tatus) = MAX(F0.foobar_s tatus)
THEN MIN(foobar_stat us)
ELSE (SELECT MIN(F1.foobar_s tatus)
FROM Foobar AS F1
WHERE F1.foobar_statu s[color=blue]
> (SELECT MIN(F2.foobar_s tatus)[/color]
FROM Foobar AS F2
WHERE F2.sedol = F0.sedol))
END
FROM Foobar AS F0
GROUP BY F0.sedol;

[Will Honea]

Re: Select the 2nd of a sequence of rows in a table

On Wed, 15 Mar 2006 20:22:11 UTC "--CELKO--" <jcelko212@eart hlink.net>
wrote:
[color=blue]
> Let's get back to the basics of an RDBMS. Rows are not records; fields
> are not columns; tables are not files; there is no sequential access or
> ordering in an RDBMS, so "first", "next" and "last" are totally
> meaningless. If you want an ordering, then you need to have a column
> that defines that ordering. You must use an ORDER BY clause on a
> cursor or in an OVER() clause.
>
> Next, please the good manners to post DDL for people who are doing your
> job or homework for free. Here is an answer in pure SQL-92, without
> SQL-99 OLAP features.[/color]

I'll try and be precise <g>. What you are doing brings to mind
another situation involving one-to-many and many-to-one situations
that I have been brute forcing for years with coding inside the
application. The specific case here is a membership list. Every
member has a unique identifier but names, addresses, and (especially)
email addresses may be duplicated (the case of multiple family members
using a common email address). What I could really use is a clean
way to select any one of the resulting multiple matches when the only
search critierion is the email address. If it would work, qualifying
the selection with FETCH FIRST 1 ROWS ONLY would be ideal - it is the
exact logic I want- but that does not work for an update function
when I try and use a list of email addresses to give the user a
contact list from the email reply list he hands me.

I'm using V 7.2 here and the master list is essentially a single
table, so is there a straight forward way to do this in dynamic SQL?

--
Will Honea

[glenn.robinson@quattroconsulting.co.uk]

Re: Select the 2nd of a sequence of rows in a table

Knut,

This looks like the best solution for my situation, thanks.

Because the system is still at V5R2 I don't have OLAP functions
available.

Glenn

[Knut Stolze]

Re: Select the 2nd of a sequence of rows in a table

--CELKO-- wrote:
[color=blue]
> Let's get back to the basics of an RDBMS. Rows are not records; fields
> are not columns; tables are not files; there is no sequential access or
> ordering in an RDBMS, so "first", "next" and "last" are totally
> meaningless. If you want an ordering, then you need to have a column
> that defines that ordering.[/color]

You have to have an _expression_ that defines the ordering. ;-)

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Knut Stolze]

Re: Select the 2nd of a sequence of rows in a table

glenn.robinson@ quattroconsulti ng.co.uk wrote:
[color=blue]
> Knut,
>
> This looks like the best solution for my situation, thanks.
>
> Because the system is still at V5R2 I don't have OLAP functions
> available.[/color]

Let us know if this does not work. I just typed it in without verifying the
syntax...

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Glenn Robinson]

Re: Select the 2nd of a sequence of rows in a table

Knut Stolze wrote:[color=blue]
> glenn.robinson@ quattroconsulti ng.co.uk wrote:
>
>[color=green]
>>Knut,
>>
>>This looks like the best solution for my situation, thanks.
>>
>>Because the system is still at V5R2 I don't have OLAP functions
>>available.[/color]
>
>
> Let us know if this does not work. I just typed it in without verifying the
> syntax...
>[/color]

We did try this yesterday but we had to coancel it after 1.5 hours, it
was still running. The table has about 250,000 roes so it's not that big.

I think I've come to the decision that I'm better off using an HLL to do
this.

I think I can use an ILE RPG porgram of about 20 lines to do this. Right
tool for the job I believe.

Thanks anyway.


Glenn