How to restrict records selected

[MFCLARKE]

I have a table with two columns.

The first columns will contain repeating values and the 2nd column will contain data relating to the 1st column value.

For each repeating value in the first column, there could be between 3 and 20 relating values in the second column.

I am looking to select up to a maximum of 10 of each repeating value from the 1st column with the corresponding value from the second column. If there are more than 10 repeating values, any 10 can be returned.

I would be happy to use any functions or additional calculated fields to help solve the issue.

I'm desperate!!

Thanks for any help.

 

[blom0344]

What type of information do these columns store?
Assuming the second column holds numeric data, you could use the rank function:

table: column1, column2

select temp.x,temp.y from
(select column1 as x,
column2 as y,
rank() over (partition by column1 order by column2)
as rank#) temp
where temp.rank# <= 10

rank function is available from DB2 UDB 7.1 onwards, so possibly for you too.........

T. Blom
Information analyst
tbl@shimano-eu.com

 

[MFCLARKE]

Thanks very much for that.

I'll give it a try.