By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,730 Members | 1,500 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,730 IT Pros & Developers. It's quick & easy.

how to extract one row from each range data

P: n/a
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?
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
------------------------------ 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.

Nov 12 '05 #2

P: n/a
------------------------------ 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.

Nov 12 '05 #3

P: n/a
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.

Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.