SQL Question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Michel Esber - 15 Feb 2007 19:23 GMT

Hello,

DB2 V8 LUW.

I have a simple table A with fields ID and SESSION_NUMBER (integer).
One ID can have multiple session numbers.

For a given ID, I can find the last two sessions with a simple SQL:

select * from table where ID='xyz' order by SESSION_NUMBER desc fetch
first two rows only

This may be rather simple, but I am stuck:

How do I return only the last two sessions for each ID that this table
has?

TIA, Michel

Reply to this Message

mikelbell2000 - 15 Feb 2007 20:30 GMT

Just did something similar. Look up the OLAP rownumber() function.
Partition by your ID field and order by your SESSION_NUMBER
descending. Feed the results of this to an outer query and look for
rows having a rownumber of 2 or less. Something like:

select id, session_number
from (
select id, session_number, rownumber() over (partition by id order
by sess desc) as rn
from table_A
) as tt
where tt.rn <=2

HTH,
Mike

> Hello,
>
[quoted text clipped - 14 lines]
>
> TIA, Michel

Reply to this Message

Mark A - 16 Feb 2007 06:38 GMT

> Hello,
>
[quoted text clipped - 14 lines]
>
> TIA, Michel

select * from table a
where SESSION_NUMBER in
(select b.SESSION_NUMBER from table b
where b.ID = a.ID
order by b.SESSION_NUMBER desc
fetch first 2 rows only)

Reply to this Message

Brian Tkatch - 16 Feb 2007 15:04 GMT

>Hello,
>
[quoted text clipped - 14 lines]
>
>TIA, Michel

By adding a DATE COLUMN, then use ORDER BY. Without an ORDER BY in the
query, there is no real order, and the results may change in between
executions.

B.

Reply to this Message

Mark A - 16 Feb 2007 17:03 GMT

> By adding a DATE COLUMN, then use ORDER BY. Without an ORDER BY in the
> query, there is no real order, and the results may change in between
> executions.

I think it is reasonable to assume that the SESSION_NUMBER is assigned
sequentially for each ID, and the two highest values are the last two
inserted for a given ID.

Reply to this Message