Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Range of values by item

Range of values by item

From: Tim Frawley <tim.frawley_at_alaska.gov>
Date: Mon, 02 Jul 2007 15:06:14 -0700
Message-ID: <1183413974.423176.289200@o11g2000prd.googlegroups.com> 





Hello all, I am hoping someone likes a challenge and is willing to
help me out.



I have data that looks like so:



create table READING (specimen_id varchar2(3), reading_number
varchar2(1), reader varchar2(3));


insert into READING values ('1','1','MWLOVEJOY');
insert into READING values ('1','2','KFVAN_KIRK');
insert into READING values ('2','1','MWLOVEJOY');
insert into READING values ('2','2','KFVAN_KIRK');
insert into READING values ('3','1','MWLOVEJOY');
insert into READING values ('3','2','KFVAN_KIRK');
insert into READING values ('4','1','MWLOVEJOY');
insert into READING values ('4','2','KFVAN_KIRK');
insert into READING values ('5','1','MWLOVEJOY');
insert into READING values ('5','2','KFVAN_KIRK');
insert into READING values ('6','1','MWLOVEJOY');
insert into READING values ('6','2','KFVAN_KIRK');
insert into READING values ('7','1','MWLOVEJOY');
insert into READING values ('7','2','KFVAN_KIRK');
insert into READING values ('8','1','MWLOVEJOY');
insert into READING values ('8','2','KFVAN_KIRK');
insert into READING values ('9','1','MWLOVEJOY');
insert into READING values ('9','2','KFVAN_KIRK');
insert into READING values ('10','1','MWLOVEJOY');
insert into READING values ('10','2','KFVAN_KIRK');
insert into READING values ('11','1','MWLOVEJOY');
insert into READING values ('11','2','KFVAN_KIRK');
insert into READING values ('12','1','MWLOVEJOY');
insert into READING values ('12','2','KFVAN_KIRK');
insert into READING values ('13','1','MWLOVEJOY');
insert into READING values ('13','2','KFVAN_KIRK');
insert into READING values ('14','1','MWLOVEJOY');
insert into READING values ('14','2','KFVAN_KIRK');
insert into READING values ('15','1','MWLOVEJOY');
insert into READING values ('15','2','KFVAN_KIRK');
insert into READING values ('16','1','MWLOVEJOY');
insert into READING values ('16','2','KFVAN_KIRK');
insert into READING values ('17','1','MWLOVEJOY');
insert into READING values ('17','2','KFVAN_KIRK');
insert into READING values ('18','1','MWLOVEJOY');
insert into READING values ('18','2','KFVAN_KIRK');
insert into READING values ('19','1','MWLOVEJOY');
insert into READING values ('19','2','KFVAN_KIRK');
insert into READING values ('20','1','MWLOVEJOY');
insert into READING values ('20','2','KFVAN_KIRK');
insert into READING values ('21','1','MWLOVEJOY');
insert into READING values ('21','2','KFVAN_KIRK');
insert into READING values ('22','1','MWLOVEJOY');
insert into READING values ('22','2','KFVAN_KIRK');
insert into READING values ('23','1','MWLOVEJOY');
insert into READING values ('23','2','KFVAN_KIRK');
insert into READING values ('24','1','MWLOVEJOY');
insert into READING values ('24','2','KFVAN_KIRK');
insert into READING values ('25','1','MWLOVEJOY');
insert into READING values ('25','2','KFVAN_KIRK');
insert into READING values ('26','1','MWLOVEJOY');
insert into READING values ('26','2','KFVAN_KIRK');
insert into READING values ('27','1','MWLOVEJOY');
insert into READING values ('27','2','KFVAN_KIRK');
insert into READING values ('28','1','MWLOVEJOY');
insert into READING values ('28','2','KFVAN_KIRK');
insert into READING values ('29','1','MWLOVEJOY');
insert into READING values ('29','2','KFVAN_KIRK');
insert into READING values ('30','1','MWLOVEJOY');
insert into READING values ('30','2','KFVAN_KIRK');
insert into READING values ('31','1','MWLOVEJOY');
insert into READING values ('31','2','KFVAN_KIRK');
insert into READING values ('32','1','MWLOVEJOY');
insert into READING values ('32','2','KFVAN_KIRK');
insert into READING values ('33','1','MWLOVEJOY');
insert into READING values ('33','2','KFVAN_KIRK');
insert into READING values ('34','1','MWLOVEJOY');
insert into READING values ('34','2','KFVAN_KIRK');
insert into READING values ('35','1','MWLOVEJOY');
insert into READING values ('35','2','KFVAN_KIRK');
insert into READING values ('36','1','MWLOVEJOY');
insert into READING values ('36','2','KFVAN_KIRK');
insert into READING values ('37','1','MWLOVEJOY');
insert into READING values ('37','2','KFVAN_KIRK');
insert into READING values ('38','1','MWLOVEJOY');
insert into READING values ('38','2','KFVAN_KIRK');
insert into READING values ('39','1','MWLOVEJOY');
insert into READING values ('39','2','KFVAN_KIRK');
insert into READING values ('40','1','MWLOVEJOY');
insert into READING values ('40','2','KFVAN_KIRK');
insert into READING values ('41','1','MWLOVEJOY');
insert into READING values ('41','2','KFVAN_KIRK');
insert into READING values ('42','1','MWLOVEJOY');
insert into READING values ('42','2','KFVAN_KIRK');
insert into READING values ('43','1','MWLOVEJOY');
insert into READING values ('43','2','KFVAN_KIRK');
insert into READING values ('44','1','MWLOVEJOY');
insert into READING values ('44','2','KFVAN_KIRK');
insert into READING values ('45','1','MWLOVEJOY');
insert into READING values ('45','2','KFVAN_KIRK');
insert into READING values ('46','1','MWLOVEJOY');
insert into READING values ('46','2','KFVAN_KIRK');
insert into READING values ('47','1','MWLOVEJOY');
insert into READING values ('47','2','KFVAN_KIRK');
insert into READING values ('48','1','MWLOVEJOY');
insert into READING values ('48','2','KFVAN_KIRK');
insert into READING values ('49','1','MWLOVEJOY');
insert into READING values ('49','2','KFVAN_KIRK');
insert into READING values ('50','1','MWLOVEJOY');
insert into READING values ('50','2','KFVAN_KIRK');
insert into READING values ('51','1','MWLOVEJOY');
insert into READING values ('51','2','KFVAN_KIRK');
insert into READING values ('52','1','MWLOVEJOY');
insert into READING values ('52','2','KFVAN_KIRK');
insert into READING values ('53','1','MWLOVEJOY');
insert into READING values ('53','2','KFVAN_KIRK');
insert into READING values ('54','1','MWLOVEJOY');
insert into READING values ('54','2','KFVAN_KIRK');
insert into READING values ('55','1','MWLOVEJOY');
insert into READING values ('55','2','KFVAN_KIRK');
insert into READING values ('56','1','MWLOVEJOY');
insert into READING values ('56','2','KFVAN_KIRK');
insert into READING values ('57','1','MWLOVEJOY');
insert into READING values ('57','2','KFVAN_KIRK');
insert into READING values ('58','1','MWLOVEJOY');
insert into READING values ('58','2','KFVAN_KIRK');
insert into READING values ('59','1','MWLOVEJOY');
insert into READING values ('59','2','KFVAN_KIRK');
insert into READING values ('60','1','MWLOVEJOY');
insert into READING values ('60','2','KFVAN_KIRK');
insert into READING values ('61','1','MWLOVEJOY');
insert into READING values ('61','2','KFVAN_KIRK');
insert into READING values ('62','1','MWLOVEJOY');
insert into READING values ('62','2','KFVAN_KIRK');
insert into READING values ('63','1','MWLOVEJOY');
insert into READING values ('63','2','KFVAN_KIRK');
insert into READING values ('64','1','MWLOVEJOY');
insert into READING values ('64','2','KFVAN_KIRK');
insert into READING values ('65','1','MWLOVEJOY');
insert into READING values ('65','2','KFVAN_KIRK');
insert into READING values ('66','1','MWLOVEJOY');
insert into READING values ('66','2','KFVAN_KIRK');
insert into READING values ('67','1','MWLOVEJOY');
insert into READING values ('67','2','KFVAN_KIRK');
insert into READING values ('68','1','MWLOVEJOY');
insert into READING values ('68','2','KFVAN_KIRK');
insert into READING values ('69','1','MWLOVEJOY');
insert into READING values ('69','2','KFVAN_KIRK');
insert into READING values ('70','1','MWLOVEJOY');
insert into READING values ('70','2','KFVAN_KIRK');
insert into READING values ('71','1','MWLOVEJOY');
insert into READING values ('71','2','KFVAN_KIRK');
insert into READING values ('72','1','MWLOVEJOY');
insert into READING values ('72','2','KFVAN_KIRK');
insert into READING values ('73','1','MWLOVEJOY');
insert into READING values ('73','2','KFVAN_KIRK');
insert into READING values ('74','1','MWLOVEJOY');
insert into READING values ('74','2','KFVAN_KIRK');
insert into READING values ('75','1','MWLOVEJOY');
insert into READING values ('75','2','KFVAN_KIRK');
insert into READING values ('76','1','MWLOVEJOY');
insert into READING values ('76','2','KFVAN_KIRK');
insert into READING values ('77','1','MWLOVEJOY');
insert into READING values ('77','2','KFVAN_KIRK');
insert into READING values ('78','1','MWLOVEJOY');
insert into READING values ('78','2','KFVAN_KIRK');
insert into READING values ('79','1','MWLOVEJOY');
insert into READING values ('79','2','KFVAN_KIRK');
insert into READING values ('80','1','MWLOVEJOY');
insert into READING values ('80','2','KFVAN_KIRK');
insert into READING values ('81','1','MWLOVEJOY');
insert into READING values ('81','2','KFVAN_KIRK');
insert into READING values ('82','1','MWLOVEJOY');
insert into READING values ('82','2','KFVAN_KIRK');
insert into READING values ('83','1','MWLOVEJOY');
insert into READING values ('83','2','KFVAN_KIRK');
insert into READING values ('84','1','MWLOVEJOY');
insert into READING values ('84','2','KFVAN_KIRK');
insert into READING values ('85','1','MWLOVEJOY');
insert into READING values ('85','2','KFVAN_KIRK');
insert into READING values ('86','1','MWLOVEJOY');
insert into READING values ('86','2','KFVAN_KIRK');
insert into READING values ('87','1','MWLOVEJOY');
insert into READING values ('87','2','KFVAN_KIRK');
insert into READING values ('88','1','MWLOVEJOY');
insert into READING values ('88','2','KFVAN_KIRK');
insert into READING values ('89','1','MWLOVEJOY');
insert into READING values ('89','2','KFVAN_KIRK');
insert into READING values ('90','1','MWLOVEJOY');
insert into READING values ('90','2','KFVAN_KIRK');
insert into READING values ('91','1','MWLOVEJOY');
insert into READING values ('91','2','KFVAN_KIRK');
insert into READING values ('92','1','MWLOVEJOY');
insert into READING values ('92','2','KFVAN_KIRK');
insert into READING values ('93','1','MWLOVEJOY');
insert into READING values ('93','2','KFVAN_KIRK');
insert into READING values ('94','1','MWLOVEJOY');
insert into READING values ('94','2','KFVAN_KIRK');
insert into READING values ('95','1','MWLOVEJOY');
insert into READING values ('95','2','KFVAN_KIRK');
insert into READING values ('96','1','MWLOVEJOY');
insert into READING values ('96','2','KFVAN_KIRK');
insert into READING values ('97','1','MWLOVEJOY');
insert into READING values ('97','2','KFVAN_KIRK');
insert into READING values ('98','1','MWLOVEJOY');
insert into READING values ('98','2','KFVAN_KIRK');
insert into READING values ('99','1','MWLOVEJOY');
insert into READING values ('99','2','KFVAN_KIRK');
insert into READING values ('100','1','MWLOVEJOY');
insert into READING values ('100','2','KFVAN_KIRK');
insert into READING values ('101','1','KFVAN_KIRK');
insert into READING values ('101','2','JWCASHEN');
insert into READING values ('102','1','KFVAN_KIRK');
insert into READING values ('102','2','JWCASHEN');
insert into READING values ('103','1','KFVAN_KIRK');
insert into READING values ('103','2','JWCASHEN');
insert into READING values ('104','1','KFVAN_KIRK');
insert into READING values ('104','2','JWCASHEN');
insert into READING values ('105','1','KFVAN_KIRK');
insert into READING values ('105','2','JWCASHEN');
insert into READING values ('106','1','KFVAN_KIRK');
insert into READING values ('106','2','JWCASHEN');
insert into READING values ('107','1','KFVAN_KIRK');
insert into READING values ('107','2','JWCASHEN');
insert into READING values ('108','1','KFVAN_KIRK');
insert into READING values ('108','2','JWCASHEN');
insert into READING values ('109','1','KFVAN_KIRK');
insert into READING values ('109','2','JWCASHEN');
insert into READING values ('110','1','KFVAN_KIRK');
insert into READING values ('110','2','JWCASHEN');
insert into READING values ('111','1','KFVAN_KIRK');
insert into READING values ('111','2','JWCASHEN');
insert into READING values ('112','1','KFVAN_KIRK');
insert into READING values ('112','2','JWCASHEN');
insert into READING values ('113','1','KFVAN_KIRK');
insert into READING values ('113','2','JWCASHEN');
insert into READING values ('114','1','KFVAN_KIRK');
insert into READING values ('114','2','JWCASHEN');
insert into READING values ('115','1','KFVAN_KIRK');
insert into READING values ('115','2','JWCASHEN');
insert into READING values ('116','1','KFVAN_KIRK');
insert into READING values ('116','2','JWCASHEN');
insert into READING values ('117','1','KFVAN_KIRK');
insert into READING values ('117','2','JWCASHEN');
insert into READING values ('118','1','KFVAN_KIRK');
insert into READING values ('118','2','JWCASHEN');
insert into READING values ('119','1','KFVAN_KIRK');
insert into READING values ('119','2','JWCASHEN');
insert into READING values ('120','1','KFVAN_KIRK');
insert into READING values ('120','2','JWCASHEN');
insert into READING values ('121','1','KFVAN_KIRK');
insert into READING values ('121','2','JWCASHEN');
insert into READING values ('122','1','KFVAN_KIRK');
insert into READING values ('122','2','JWCASHEN');
insert into READING values ('123','1','KFVAN_KIRK');
insert into READING values ('123','2','JWCASHEN');
insert into READING values ('124','1','KFVAN_KIRK');
insert into READING values ('124','2','JWCASHEN');
insert into READING values ('125','1','KFVAN_KIRK');
insert into READING values ('125','2','JWCASHEN');
insert into READING values ('126','1','KFVAN_KIRK');
insert into READING values ('126','2','JWCASHEN');
insert into READING values ('127','1','KFVAN_KIRK');
insert into READING values ('127','2','JWCASHEN');
insert into READING values ('128','1','KFVAN_KIRK');
insert into READING values ('128','2','JWCASHEN');
insert into READING values ('129','1','KFVAN_KIRK');
insert into READING values ('129','2','JWCASHEN');
insert into READING values ('130','1','KFVAN_KIRK');
insert into READING values ('130','2','JWCASHEN');
insert into READING values ('131','1','KFVAN_KIRK');
insert into READING values ('131','2','JWCASHEN');
insert into READING values ('132','1','KFVAN_KIRK');
insert into READING values ('132','2','JWCASHEN');
insert into READING values ('133','1','KFVAN_KIRK');
insert into READING values ('133','2','JWCASHEN');
insert into READING values ('134','1','KFVAN_KIRK');
insert into READING values ('134','2','JWCASHEN');
insert into READING values ('135','1','KFVAN_KIRK');
insert into READING values ('135','2','JWCASHEN');
insert into READING values ('136','1','KFVAN_KIRK');
insert into READING values ('136','2','JWCASHEN');
insert into READING values ('137','1','KFVAN_KIRK');
insert into READING values ('137','2','JWCASHEN');
insert into READING values ('138','1','KFVAN_KIRK');
insert into READING values ('138','2','JWCASHEN');
insert into READING values ('139','1','KFVAN_KIRK');
insert into READING values ('139','2','JWCASHEN');
insert into READING values ('140','1','KFVAN_KIRK');
insert into READING values ('140','2','JWCASHEN');
insert into READING values ('141','1','KFVAN_KIRK');
insert into READING values ('141','2','JWCASHEN');
insert into READING values ('142','1','KFVAN_KIRK');
insert into READING values ('142','2','JWCASHEN');
insert into READING values ('143','1','KFVAN_KIRK');
insert into READING values ('143','2','JWCASHEN');
insert into READING values ('144','1','KFVAN_KIRK');
insert into READING values ('144','2','JWCASHEN');
insert into READING values ('145','1','KFVAN_KIRK');
insert into READING values ('145','2','JWCASHEN');
insert into READING values ('146','1','KFVAN_KIRK');
insert into READING values ('146','2','JWCASHEN');
insert into READING values ('147','1','KFVAN_KIRK');
insert into READING values ('147','2','JWCASHEN');
insert into READING values ('148','1','KFVAN_KIRK');
insert into READING values ('148','2','JWCASHEN');
insert into READING values ('149','1','KFVAN_KIRK');
insert into READING values ('149','2','JWCASHEN');
insert into READING values ('150','1','KFVAN_KIRK');
insert into READING values ('150','2','JWCASHEN');
insert into READING values ('151','1','KFVAN_KIRK');
insert into READING values ('151','2','JWCASHEN');
insert into READING values ('152','1','KFVAN_KIRK');
insert into READING values ('152','2','JWCASHEN');
insert into READING values ('153','1','KFVAN_KIRK');
insert into READING values ('153','2','JWCASHEN');
insert into READING values ('154','1','KFVAN_KIRK');
insert into READING values ('154','2','JWCASHEN');
insert into READING values ('155','1','KFVAN_KIRK');
insert into READING values ('155','2','JWCASHEN');
insert into READING values ('156','1','KFVAN_KIRK');
insert into READING values ('156','2','JWCASHEN');
insert into READING values ('157','1','KFVAN_KIRK');
insert into READING values ('157','2','JWCASHEN');
insert into READING values ('158','1','KFVAN_KIRK');
insert into READING values ('158','2','JWCASHEN');
insert into READING values ('159','1','KFVAN_KIRK');
insert into READING values ('159','2','JWCASHEN');
insert into READING values ('160','1','KFVAN_KIRK');
insert into READING values ('160','2','JWCASHEN');
insert into READING values ('161','1','KFVAN_KIRK');
insert into READING values ('161','2','JWCASHEN');
insert into READING values ('162','1','KFVAN_KIRK');
insert into READING values ('162','2','JWCASHEN');
insert into READING values ('163','1','KFVAN_KIRK');
insert into READING values ('163','2','JWCASHEN');
insert into READING values ('164','1','KFVAN_KIRK');
insert into READING values ('164','2','JWCASHEN');
insert into READING values ('165','1','KFVAN_KIRK');
insert into READING values ('165','2','JWCASHEN');
insert into READING values ('166','1','KFVAN_KIRK');
insert into READING values ('166','2','JWCASHEN');
insert into READING values ('167','1','KFVAN_KIRK');
insert into READING values ('167','2','JWCASHEN');
insert into READING values ('168','1','KFVAN_KIRK');
insert into READING values ('168','2','JWCASHEN');
insert into READING values ('169','1','KFVAN_KIRK');
insert into READING values ('169','2','JWCASHEN');
insert into READING values ('170','1','KFVAN_KIRK');
insert into READING values ('170','2','JWCASHEN');
insert into READING values ('171','1','KFVAN_KIRK');
insert into READING values ('171','2','JWCASHEN');
insert into READING values ('172','1','KFVAN_KIRK');
insert into READING values ('172','2','JWCASHEN');
insert into READING values ('173','1','KFVAN_KIRK');
insert into READING values ('173','2','JWCASHEN');
insert into READING values ('174','1','KFVAN_KIRK');
insert into READING values ('174','2','JWCASHEN');
insert into READING values ('175','1','KFVAN_KIRK');
insert into READING values ('175','2','JWCASHEN');
insert into READING values ('176','1','KFVAN_KIRK');
insert into READING values ('176','2','JWCASHEN');
insert into READING values ('177','1','KFVAN_KIRK');
insert into READING values ('177','2','JWCASHEN');
insert into READING values ('178','1','KFVAN_KIRK');
insert into READING values ('178','2','JWCASHEN');
insert into READING values ('179','1','KFVAN_KIRK');
insert into READING values ('179','2','JWCASHEN');
insert into READING values ('180','1','KFVAN_KIRK');
insert into READING values ('180','2','JWCASHEN');
insert into READING values ('181','1','KFVAN_KIRK');
insert into READING values ('181','2','JWCASHEN');
insert into READING values ('182','1','KFVAN_KIRK');
insert into READING values ('182','2','JWCASHEN');
insert into READING values ('183','1','KFVAN_KIRK');
insert into READING values ('183','2','JWCASHEN');
insert into READING values ('184','1','KFVAN_KIRK');
insert into READING values ('184','2','JWCASHEN');
insert into READING values ('185','1','KFVAN_KIRK');
insert into READING values ('185','2','JWCASHEN');
insert into READING values ('186','1','KFVAN_KIRK');
insert into READING values ('186','2','JWCASHEN');
insert into READING values ('187','1','KFVAN_KIRK');
insert into READING values ('187','2','JWCASHEN');
insert into READING values ('188','1','KFVAN_KIRK');
insert into READING values ('188','2','JWCASHEN');
insert into READING values ('189','1','KFVAN_KIRK');
insert into READING values ('189','2','JWCASHEN');
insert into READING values ('190','1','KFVAN_KIRK');
insert into READING values ('190','2','JWCASHEN');
insert into READING values ('191','1','KFVAN_KIRK');
insert into READING values ('192','1','KFVAN_KIRK');
insert into READING values ('193','1','KFVAN_KIRK');
insert into READING values ('194','1','KFVAN_KIRK');
insert into READING values ('195','1','KFVAN_KIRK');
insert into READING values ('196','1','KFVAN_KIRK');
insert into READING values ('197','1','KFVAN_KIRK');
insert into READING values ('198','1','KFVAN_KIRK');
insert into READING values ('199','1','KFVAN_KIRK');
insert into READING values ('200','1','KFVAN_KIRK');
insert into READING values ('201','1','MWLOVEJOY');
insert into READING values ('202','1','MWLOVEJOY');
insert into READING values ('203','1','MWLOVEJOY');
insert into READING values ('204','1','MWLOVEJOY');
insert into READING values ('205','1','MWLOVEJOY');
insert into READING values ('206','1','MWLOVEJOY');
insert into READING values ('207','1','MWLOVEJOY');
insert into READING values ('208','1','MWLOVEJOY');
insert into READING values ('209','1','MWLOVEJOY');
insert into READING values ('210','1','MWLOVEJOY');
insert into READING values ('211','1','MWLOVEJOY');
insert into READING values ('212','1','MWLOVEJOY');
insert into READING values ('213','1','MWLOVEJOY');
insert into READING values ('214','1','MWLOVEJOY');
insert into READING values ('215','1','MWLOVEJOY');
insert into READING values ('216','1','MWLOVEJOY');
insert into READING values ('217','1','MWLOVEJOY');
insert into READING values ('218','1','MWLOVEJOY');
insert into READING values ('219','1','MWLOVEJOY');
insert into READING values ('220','1','MWLOVEJOY');
insert into READING values ('221','1','MWLOVEJOY');
insert into READING values ('222','1','MWLOVEJOY');
insert into READING values ('223','1','MWLOVEJOY');
insert into READING values ('224','1','MWLOVEJOY');
insert into READING values ('225','1','MWLOVEJOY');
insert into READING values ('226','1','MWLOVEJOY');
insert into READING values ('227','1','MWLOVEJOY');
insert into READING values ('228','1','MWLOVEJOY');
insert into READING values ('229','1','MWLOVEJOY');
insert into READING values ('230','1','MWLOVEJOY');
insert into READING values ('231','1','MWLOVEJOY');
insert into READING values ('232','1','MWLOVEJOY');
insert into READING values ('233','1','MWLOVEJOY');
insert into READING values ('234','1','MWLOVEJOY');
insert into READING values ('235','1','MWLOVEJOY');
insert into READING values ('236','1','MWLOVEJOY');
insert into READING values ('237','1','MWLOVEJOY');
insert into READING values ('238','1','MWLOVEJOY');
insert into READING values ('239','1','MWLOVEJOY');
insert into READING values ('240','1','MWLOVEJOY');
insert into READING values ('241','1','MWLOVEJOY');
insert into READING values ('242','1','MWLOVEJOY');
insert into READING values ('243','1','MWLOVEJOY');
insert into READING values ('244','1','MWLOVEJOY');
insert into READING values ('245','1','MWLOVEJOY');
insert into READING values ('246','1','MWLOVEJOY');
insert into READING values ('247','1','MWLOVEJOY');
insert into READING values ('248','1','MWLOVEJOY');
insert into READING values ('249','1','MWLOVEJOY');
insert into READING values ('250','1','MWLOVEJOY');
insert into READING values ('251','1','MWLOVEJOY');
insert into READING values ('252','1','MWLOVEJOY');
insert into READING values ('253','1','MWLOVEJOY');
insert into READING values ('254','1','MWLOVEJOY');
insert into READING values ('255','1','MWLOVEJOY');
insert into READING values ('256','1','MWLOVEJOY');
insert into READING values ('257','1','MWLOVEJOY');
insert into READING values ('258','1','MWLOVEJOY');
insert into READING values ('259','1','MWLOVEJOY');
insert into READING values ('260','1','MWLOVEJOY');
insert into READING values ('261','1','MWLOVEJOY');
insert into READING values ('262','1','MWLOVEJOY');
insert into READING values ('263','1','MWLOVEJOY');
insert into READING values ('264','1','MWLOVEJOY');
insert into READING values ('265','1','MWLOVEJOY');
insert into READING values ('266','1','MWLOVEJOY');
insert into READING values ('267','1','MWLOVEJOY');
insert into READING values ('268','1','MWLOVEJOY');
insert into READING values ('269','1','MWLOVEJOY');
insert into READING values ('270','1','MWLOVEJOY');
insert into READING values ('271','1','MWLOVEJOY');
insert into READING values ('272','1','MWLOVEJOY');
insert into READING values ('273','1','MWLOVEJOY');
insert into READING values ('274','1','MWLOVEJOY');
insert into READING values ('275','1','MWLOVEJOY');
insert into READING values ('276','1','MWLOVEJOY');
insert into READING values ('277','1','MWLOVEJOY');
insert into READING values ('278','1','MWLOVEJOY');
insert into READING values ('279','1','MWLOVEJOY');
insert into READING values ('280','1','MWLOVEJOY');
insert into READING values ('281','1','MWLOVEJOY');
insert into READING values ('282','1','MWLOVEJOY');
insert into READING values ('283','1','MWLOVEJOY');
insert into READING values ('284','1','MWLOVEJOY');
insert into READING values ('285','1','MWLOVEJOY');
insert into READING values ('286','1','MWLOVEJOY');
insert into READING values ('287','1','MWLOVEJOY');
insert into READING values ('288','1','MWLOVEJOY');
insert into READING values ('289','1','MWLOVEJOY');
insert into READING values ('290','1','MWLOVEJOY');
insert into READING values ('291','1','MWLOVEJOY');
insert into READING values ('292','1','MWLOVEJOY');
insert into READING values ('293','1','MWLOVEJOY');
insert into READING values ('294','1','MWLOVEJOY');
insert into READING values ('295','1','MWLOVEJOY');
insert into READING values ('296','1','MWLOVEJOY');
insert into READING values ('297','1','MWLOVEJOY');
insert into READING values ('298','1','MWLOVEJOY');
insert into READING values ('299','1','MWLOVEJOY');
insert into READING values ('300','1','MWLOVEJOY');
insert into READING values ('301','1','JWCASHEN');
insert into READING values ('302','1','JWCASHEN');
insert into READING values ('303','1','JWCASHEN');
insert into READING values ('304','1','JWCASHEN');
insert into READING values ('305','1','JWCASHEN');
insert into READING values ('306','1','JWCASHEN');
insert into READING values ('307','1','JWCASHEN');
insert into READING values ('308','1','JWCASHEN');
insert into READING values ('309','1','JWCASHEN');
insert into READING values ('310','1','JWCASHEN');
insert into READING values ('311','1','JWCASHEN');
insert into READING values ('312','1','JWCASHEN');
insert into READING values ('313','1','JWCASHEN');
insert into READING values ('314','1','JWCASHEN');
insert into READING values ('315','1','JWCASHEN');
insert into READING values ('316','1','JWCASHEN');
insert into READING values ('317','1','JWCASHEN');
insert into READING values ('318','1','JWCASHEN');
insert into READING values ('319','1','JWCASHEN');
insert into READING values ('320','1','JWCASHEN');
insert into READING values ('321','1','JWCASHEN');
insert into READING values ('322','1','JWCASHEN');
insert into READING values ('323','1','JWCASHEN');
insert into READING values ('324','1','JWCASHEN');
insert into READING values ('325','1','JWCASHEN');
insert into READING values ('326','1','JWCASHEN');
insert into READING values ('327','1','JWCASHEN');
insert into READING values ('328','1','JWCASHEN');
insert into READING values ('329','1','JWCASHEN');
insert into READING values ('330','1','JWCASHEN');






This is the SQL I am using which works but doesn't display the second
group of read results for MWLOVEJOY, it lumps those reads in with
KFVAN_KIRK.  See the results at the very bottom.




select distinct START_


                ,END_


                ,READER




 from (

       select
                first_value(SPECIMEN_ID) over (partition by DIFF)
START_


                ,last_value(SPECIMEN_ID) over (partition by DIFF) END_

                ,READER

                ,row_number() over (partition by DIFF order by

SPECIMEN_ID) R
       from (
                select
                       row_number() over(partition by READER order by
SPECIMEN_ID) - SPECIMEN_ID  DIFF
                       ,READER
                       ,SPECIMEN_ID
                from
                       READING R1
            )
      )
 where
       R=1
 order by
       START_






Results look like:


1	001	100	MWLOVEJOY
2	101	300	KFVAN_KIRK
3	301	330	JWCASHEN






I need results that look like:


1	001	100	MWLOVEJOY
2	101	200	KFVAN_KIRK
3	201	300	MWLOVEJOY
4	301	330	JWCASHEN


Received on Mon Jul 02 2007 - 17:06:14 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US