| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Range of values by item
Comments embedded.
On Jul 2, 5:06 pm, Tim Frawley <tim.fraw..._at_alaska.gov> wrote:
> 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');
>
Which is your first 'problem' given the supposed logic of your SQL.
> 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.
This SQL does not produce the output you purport.
>
> 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
>
No, they actually look like this:
STA END READER
--- --- ------------------------------
110 119 KFVAN_KIRK 12 12 KFVAN_KIRK 120 129 KFVAN_KIRK 13 13 KFVAN_KIRK 130 139 KFVAN_KIRK
STA END READER
--- --- ------------------------------ 14 14 KFVAN_KIRK 140 149 KFVAN_KIRK 15 15 KFVAN_KIRK 150 159 KFVAN_KIRK 16 16 KFVAN_KIRK 160 169 KFVAN_KIRK 17 17 KFVAN_KIRK 170 179 KFVAN_KIRK 18 18 KFVAN_KIRK
--- --- ------------------------------
--- --- ------------------------------
--- --- ------------------------------
--- --- ------------------------------
80 89 KFVAN_KIRK 9 9 KFVAN_KIRK 90 99 KFVAN_KIRK
65 rows selected.
SQL>
> I need results that look like:
>
> 1 001 100 MWLOVEJOY
> 2 101 200 KFVAN_KIRK
> 3 201 300 MWLOVEJOY
> 4 301 330 JWCASHEN
One, you need to provide us the SQL you ARE using to generate that
'report'. Two, you need to look at what you've written as a 'source':
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
)
and how it produces your 'problem records':
STA END READER R --- --- ------------------------------ ---------- 50 59 KFVAN_KIRK 14 50 59 MWLOVEJOY 15 50 59 KFVAN_KIRK 16 50 59 MWLOVEJOY 17 50 59 KFVAN_KIRK 18 50 59 KFVAN_KIRK 19 50 59 MWLOVEJOY 20 60 69 MWLOVEJOY 1 60 69 KFVAN_KIRK 2 60 69 MWLOVEJOY 3 60 69 KFVAN_KIRK 4 STA END READER R --- --- ------------------------------ ---------- 60 69 MWLOVEJOY 5 60 69 KFVAN_KIRK 6 60 69 MWLOVEJOY 7 60 69 KFVAN_KIRK 8 60 69 MWLOVEJOY 9 60 69 KFVAN_KIRK 10 60 69 MWLOVEJOY 11 60 69 KFVAN_KIRK 12 60 69 MWLOVEJOY 13 60 69 KFVAN_KIRK 14 60 69 MWLOVEJOY 15 STA END READER R --- --- ------------------------------ ---------- 60 69 KFVAN_KIRK 16 60 69 MWLOVEJOY 17 60 69 KFVAN_KIRK 18 60 69 KFVAN_KIRK 19 60 69 MWLOVEJOY 20 70 79 MWLOVEJOY 1 70 79 KFVAN_KIRK 2 70 79 MWLOVEJOY 3 70 79 KFVAN_KIRK 4 70 79 KFVAN_KIRK 5 70 79 MWLOVEJOY 6 STA END READER R --- --- ------------------------------ ---------- 70 79 KFVAN_KIRK 7 70 79 MWLOVEJOY 8 70 79 MWLOVEJOY 9 70 79 KFVAN_KIRK 10 70 79 MWLOVEJOY 11 70 79 KFVAN_KIRK 12 70 79 KFVAN_KIRK 13 70 79 MWLOVEJOY 14 70 79 KFVAN_KIRK 15 70 79 MWLOVEJOY 16 70 79 MWLOVEJOY 17 STA END READER R --- --- ------------------------------ ---------- 70 79 KFVAN_KIRK 18 70 79 MWLOVEJOY 19 70 79 KFVAN_KIRK 20 80 89 KFVAN_KIRK 1 80 89 MWLOVEJOY 2 80 89 KFVAN_KIRK 3 80 89 MWLOVEJOY 4 80 89 MWLOVEJOY 5 80 89 KFVAN_KIRK 6 80 89 KFVAN_KIRK 7 80 89 MWLOVEJOY 8 STA END READER R --- --- ------------------------------ ---------- 80 89 KFVAN_KIRK 9 80 89 MWLOVEJOY 10 80 89 KFVAN_KIRK 11 80 89 MWLOVEJOY 12 80 89 MWLOVEJOY 13 80 89 KFVAN_KIRK 14 80 89 KFVAN_KIRK 15 80 89 MWLOVEJOY 16 80 89 KFVAN_KIRK 17 80 89 MWLOVEJOY 18 80 89 KFVAN_KIRK 19 STA END READER R --- --- ------------------------------ ---------- 80 89 MWLOVEJOY 20 90 99 KFVAN_KIRK 1 90 99 MWLOVEJOY 2 90 99 MWLOVEJOY 3 90 99 KFVAN_KIRK 4 90 99 KFVAN_KIRK 5 90 99 MWLOVEJOY 6 90 99 KFVAN_KIRK 7 90 99 MWLOVEJOY 8 90 99 KFVAN_KIRK 9 90 99 MWLOVEJOY 10 STA END READER R --- --- ------------------------------ ---------- 90 99 KFVAN_KIRK 11 90 99 MWLOVEJOY 12 90 99 KFVAN_KIRK 13 90 99 MWLOVEJOY 14 90 99 KFVAN_KIRK 15 90 99 MWLOVEJOY 16 90 99 KFVAN_KIRK 17 90 99 MWLOVEJOY 18 90 99 MWLOVEJOY 19 90 99 KFVAN_KIRK 20 2 2 KFVAN_KIRK 1 STA END READER R --- --- ------------------------------ ---------- 3 3 MWLOVEJOY 1 3 3 KFVAN_KIRK 1 4 4 MWLOVEJOY 1 4 4 KFVAN_KIRK 2 5 5 MWLOVEJOY 1 5 5 KFVAN_KIRK 2 6 6 MWLOVEJOY 1 6 6 KFVAN_KIRK 2 7 7 MWLOVEJOY 1 7 7 KFVAN_KIRK 2 8 8 MWLOVEJOY 1 STA END READER R --- --- ------------------------------ ---------- 8 8 KFVAN_KIRK 2 9 9 KFVAN_KIRK 1 9 9 MWLOVEJOY 2
Again, without your actual SQL which produces your 'report' no one can help you. And the code you posted does not produce what you say it does, as I've run it more than once on your data and the same results I posted continually appear. I'm running this under 10.2.0.3. Which release are you using?
David Fitzjarrell Received on Mon Jul 02 2007 - 20:24:52 CDT
 |
 |