how to extract one row from each range data

[janet]

hi

I want to extract one row from each range data.

For example , we had table had following data

name age department
janet 22 HR
John 45 IT
Jane 25 IT
Tom 35 HR
Nancy 33 Sales
I want to get any one row from age range 20~30, 30~40, 40~50

is there a single way to do that?

[Tonkuma]

------------------------------ Commands Entered
------------------------------
SELECT name, age, department
FROM (SELECT q.*
, ROWNUMBER() OVER(PARTITION BY age/10 ORDER BY age) rno
FROM RANGE_TEST q
) r
WHERE rno = 1
ORDER BY age;
------------------------------------------------------------------------------

NAME AGE DEPARTMENT
------- ------ ----------
janet 22 HR
Nancy 33 Sales
John 45 IT

3 record(s) selected.

[Tonkuma]

------------------------------ Commands Entered
------------------------------
SELECT L, U, name, age, department
FROM (SELECT q.*, L, U
, ROWNUMBER() OVER(PARTITION BY L ORDER BY age) rno
FROM RANGE_TEST q
, (VALUES (20,29), (30,39), (40,49)) Range(L, U)
WHERE age BETWEEN L AND U
) r
WHERE rno = 1
ORDER BY age;
------------------------------------------------------------------------------

L U NAME AGE DEPARTMENT
----------- ----------- ------- ------ ----------
20 29 janet 22 HR
30 39 Nancy 33 Sales
40 49 John 45 IT

3 record(s) selected.

[janet]

hi, Tonkuma

Thanks very much for quickly response! Both answered my question. The
second way is more flexible...
"Tonkuma" <to*****@jp.ibm.com> wrote in message news:<11**********************@g14g2000cwa.googleg roups.com>...

------------------------------ Commands Entered
------------------------------
SELECT L, U, name, age, department
FROM (SELECT q.*, L, U
, ROWNUMBER() OVER(PARTITION BY L ORDER BY age) rno
FROM RANGE_TEST q
, (VALUES (20,29), (30,39), (40,49)) Range(L, U)
WHERE age BETWEEN L AND U
) r
WHERE rno = 1
ORDER BY age;
------------------------------------------------------------------------------

L U NAME AGE DEPARTMENT
----------- ----------- ------- ------ ----------
20 29 janet 22 HR
30 39 Nancy 33 Sales
40 49 John 45 IT

3 record(s) selected.