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

[glenn.robinson]

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]

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

[aj]

<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

[glenn.robinson]

Dave,

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

Thanks for this.

Glenn

[Dave Hughes]

gl************@quattroconsulting.co.uk wrote:

Dave,

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

Thanks for this.

Glenn

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]

Hello.

If you don't afraid of java UDF mail me at
mark(dot)b(at)mail(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(char(sedol), 16) as rn
from session.t2
)
select t1.sedol, coalesce(t2.status, t1.status) as status
from t t1
left join t t2 on t1.sedol=t2.sedol 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]

Dave Hughes wrote:

gl************@quattroconsulting.co.uk wrote:

Dave,

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

Thanks for this.

Glenn

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

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]

Knut Stolze wrote:

Dave Hughes wrote:

gl************@quattroconsulting.co.uk wrote:

Dave,

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

Thanks for this.

Glenn

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

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 )

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]

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

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_status) = MAX(F0.foobar_status)
THEN MIN(foobar_status)
ELSE (SELECT MIN(F1.foobar_status)
FROM Foobar AS F1
WHERE F1.foobar_status

(SELECT MIN(F2.foobar_status)

FROM Foobar AS F2
WHERE F2.sedol = F0.sedol))
END
FROM Foobar AS F0
GROUP BY F0.sedol;

[Will Honea]

On Wed, 15 Mar 2006 20:22:11 UTC "--CELKO--" <jc*******@earthlink.net>
wrote:

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.

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]

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]

--CELKO-- wrote:

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 have to have an _expression_ that defines the ordering. ;-)

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Knut Stolze]

gl************@quattroconsulting.co.uk wrote:

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.

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]

Knut Stolze wrote:

gl************@quattroconsulting.co.uk wrote:

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.

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

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

[Brian Tkatch]

It looks as if status is always the second letter in the alphabet on a
"second" row. Is that always true?

Because, if so. a simple query such as

SELECT SEDOL, MAX(STATUS) FROM table WHERE Status IN ('A', 'B') GROUP
BY SEDOL

B.

[Tonkuma]

SELECT SEDOL
, MAX(STATUS) AS STATUS
FROM SEDOLTBL T1
WHERE (SELECT COUNT(*)
FROM SEDOLTBL T2
WHERE T2.SEDOL = T1.SEDOL
AND T2.STATUS <= T1.STATUS
) <= 2
GROUP BY
SEDOL;
---------------------------------------------------
SEDOL STATUS
----------- ------
10000 A
10001 B
10050 B
10051 A
10100 B

[Brian Tkatch]

Wow, Tonkuma, that's good. And so simple too. :)

B.