top ten values

ps_deep · #1 (**permalink**) 08-18-06, 21:23

I am a new bee in Db2, Can someone tell me how to get 10 max values from a table in Db2, earlier i was using oracle where there was rownum but in db2 w do not have this..

Thanks
Deep

jthakrar · #2 (**permalink**) 08-19-06, 09:00

I suppose you want the top 10 rows from a table, right ?
Then you can use "fetch first 10 rows only" clause at the end of your SQL.

However note that the "first 10 rows" depends on the access path that the query takes (unless you have an order by clause or something that forces the same order of rows).

Peter.Vanroose · #3 (**permalink**) 08-20-06, 14:11

Quote:

Originally Posted by ps_deep

how to get 10 max values from a table in Db2

For the 10 max values of column k of table t:

Code:

SELECT DISTINCT k FROM t
ORDER BY k DESC
FETCH FIRST 10 ROWS ONLY
OPTIMIZE FOR 1 ROW

The "standard SQL" way to obtain this would be:

Code:

SELECT t1.k FROM t AS t1, t AS t2
WHERE t1.k <= t2.k
GROUP BY t1.k
HAVING count(*) <= 10

Internet Services

Web Development

Digital Marketing

Consumer Tech