how to extract one row from each range data

janet · *nix forums beginner Joined: 22 Feb 2005 Posts: 2

hi, Tonkuma

Thanks very much for quickly response! Both answered my question. The
second way is more flexible...

"Tonkuma" <tonkuma@jp.ibm.com> wrote in message news:<1109117294.910906.298150@g14g2000cwa.googlegroups.com>...

Tonkuma · *nix forums Guru Wannabe Joined: 07 Jun 2005 Posts: 178

------------------------------ 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.

Tonkuma · *nix forums Guru Wannabe Joined: 07 Jun 2005 Posts: 178

------------------------------ 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.

janet · *nix forums beginner Joined: 22 Feb 2005 Posts: 2

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?

Google