# DB2 SQL question - first two records



## cgjoker (Aug 13, 2003)

I have a result that looks like this....

id1 21
id1 19
id1 15

id2 13
id2 11

id3 25
id3 24
id3 23
id3 22

I want to select the top two values for each id.

Any ideas?

thanks..


----------



## Chicon (Jul 29, 2004)

Hi cgjoker,

At the end of your SELECT statement, add the following words :

*FETCH FIRST 2 ROWS ONLY*


----------



## cgjoker (Aug 13, 2003)

that would give me the first two rows of the entire query.... 

id1 21
id1 19

I want the first two rows for each id. 

any other ideas?


----------



## Chicon (Jul 29, 2004)

You probably need to create a procedure using a cursor.
I guess there's no direct way to get the results you want, even using an embedded selection.
Sorry, I'm really stuck on this one !


----------



## cgjoker (Aug 13, 2003)

thanks, it is a complicated query... anyone else have any ideas?


----------



## robertwylie (Mar 29, 2007)

Complete data:

select * from t1

A B
----------- -----------
1 2
1 3
1 4
1 1
2 1
2 2
2 8

This will now get the first two rows for each group:

SELECT x.*
FROM t1 AS x,
LATERAL ( SELECT *
FROM t1 AS i
WHERE x.a = y.a
ORDER BY y.b
FETCH FIRST 2 ROWS ONLY ) AS z
WHERE z.b = x.b

The result will be :

A B
----------- -----------
1 2
1 3
2 1
2 2
May be this will help you
:up:


----------



## Chicon (Jul 29, 2004)

Hi robertwylie,

Thanks for your info !
I'm learning too : I didn't know the existence of the LATERAL thing. It looks easy to use ! :up:


----------



## cgjoker (Aug 13, 2003)

That is great.... but this will give me the same problem with some sql I came up with as well. The problem is that for a column say A, has a value that is duplicated it will output all the values when I still only want the top two. 

ex.
A B
----------- -----------
1 2
1 2
1 2
1 2
1 3
1 4
1 1
2 1
2 2
2 8

The result will be :

A B
----------- -----------
1 2
1 2
1 2
1 2
2 1
2 2 

result should be:

A B
----------- -----------
1 2
1 2
2 1
2 2


----------



## cgjoker (Aug 13, 2003)

just as an fyi.. this is how i did with the same result as what you suggested...

select a, b 
from (SELECT a, b, RANK() OVER(PARTITION BY id ORDER BY b desc) AS r1
FROM t1) as xx
where r1 <= 2 
order by id


----------



## cgjoker (Aug 13, 2003)

this seems to work....

select id, score 
from (SELECTid, score, rownumber() over (partition by id order by score desc) as rn 
from x) as tt 
where tt.rn <=2 
order by id


----------



## cgjoker (Aug 13, 2003)

okay, next question....

using the result of this query, how would i join to another table to incorporate another field.

so new table A has a field xyz. I would like the output to be

id score xyz
1 10 x
1 9 x
2 21 y
3 14 z
3 11 z


----------



## cgjoker (Aug 13, 2003)

here is my attempt.... no luck.

select x.id, x.score, z.xyz 
from 
tt o, A a
where
o.id=a.id 
o.id in
(SELECT xyz, id , score, rownumber() over (partition by id k order by score desc) as rn 
from 
tt 
) as tt 
where tt.rn <=2 
order by id


----------

