how to extract one row from each range data

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

janet - 22 Feb 2005 18:44 GMT

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?

Reply to this Message

Tonkuma - 22 Feb 2005 23:26 GMT

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

Reply to this Message

Tonkuma - 23 Feb 2005 00:08 GMT

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

Reply to this Message

janet - 25 Feb 2005 03:21 GMT

hi, Tonkuma

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

> ------------------------------ Commands Entered
> ------------------------------
[quoted text clipped - 16 lines]
>
> 3 record(s) selected.

Reply to this Message