473,574 Members | 2,388 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

how to extract one row from each range data

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
3 1399
------------------------------ 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
------------------------------ 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
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.go oglegroups.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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

9
3374
by: Brian Hanson | last post by:
Hi, I have an unusual problem that just showed its ugly head at a pretty bad time. I have an asp.net (VB) app that takes data from an Excel sheet and puts it into SQL Server. I get the data out of Excel using OleDB, and suddenly, some of the data was not being extracted from Excel. I use OleDb for the extract into a DataTable and from...
7
3128
by: fool | last post by:
Dear group, Extract the integer value present in a given string. So I tried the following: int main(void) { int val; char *data; data = malloc(sizeof *data); if(data)
7
7591
by: erikcw | last post by:
Hi all, I'm trying to extract zip file (containing an xml file) from an email so I can process it. But I'm running up against some brick walls. I've been googling and reading all afternoon, and can't seem to figure it out. Here is what I have so far. p = POP3("mail.server.com")
0
2038
by: napolpie | last post by:
DISCUSSION IN USER nappie writes: Hello, I'm Peter and I'm new in python codying and I'm using parsying to extract data from one meteo Arpege file. This file is long file and it's composed by word and number arguments like this: GRILLE EURAT5 Coin Nord-Ouest : 46.50/ 0.50 Coin Sud-E Hello, I'm Peter and I'm new in python codying and I'm...
0
5085
by: mix01 | last post by:
Hi, I am trying to get some VBA code working, but am preplex as to why it does not work. I would really appreciate any level of help. Many thanks, Mix01 Version of the program
5
2835
by: =?Utf-8?B?aWxy?= | last post by:
Hi This is probably fairly simple but I am newish at programming and was wondering if someone can give me some advice on handling the following. I have an array with a large number of elements in it. 0-9 are related data, 10-19, 20-29 are related and so on. What is the best way of extracting groups of elements from the array into...
1
5058
by: manishabh77 | last post by:
I will be obliged if anybody can help me with this problem: I am trying to extract data from an excel sheet that matches IDs given in column 4 of the excel sheet.I have stored those query IDs in an array (@names). After I look for the match in this section of the code: if ($value=~/^$names$/), I want to write out only those rows that satisfy the...
7
2100
by: JoeC | last post by:
I am trying to create a windows program that reads binary graphics as a resource. This has nothing to do with win32 but conversion of data with memcpy. graphic::graphic(UINT uiResID, HINSTANCE hinstance){ size = 32; bitData.clear(); void * p = NULL; // point to the data int end; BYTE data;
0
1619
by: =?ISO-8859-1?Q?J=F6rg_Battermann?= | last post by:
Hello there, Is there any -reliable- way to extract certain, given ranges in excel files into either a picture (each) or into proper xhtml? I am asking because I am currently automating a document transformation from several hundred thousand xhtml datasets into a .pdf via proper xml + xsl-fo transformation, but one of the constraints is to...
0
7797
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7715
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8041
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8225
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
5611
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
3739
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3748
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2233
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
0
1053
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.