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 -> Re: Returning Range of values where gaps exist

Re: Returning Range of values where gaps exist

From: TurkBear <john.greco_at_dot.state.mn.us>
Date: Mon, 28 Apr 2003 12:14:59 -0500
Message-ID: <76oqav45jtl9ti7hbh1rd3r36n0tccpi7h@4ax.com>


Try

Select reader,min(specimen_id) start,max(specimen_id) stop from
data_table
group by reader

( rownum can be awkward to use and is only assigned AFTER the rows are returned)

tim_frawley_at_fishgame.state.ak.us (Tim Frawley) wrote:

>Here is the entire set of Data I am attempting to manipulate.
>
>SPECIMEN_ID READER
>001 JWC
>002 JWC
>003 JWC
>004 JWC
>005 JWC
>006 JWC
>007 JWC
>008 JWC
>009 JWC
>010 JWC
>011 JWC
>012 JWC
>013 JWC
>014 JWC
>015 JWC
>016 JWC
>017 JWC
>018 JWC
>019 JWC
>020 JWC
>021 JWC
>022 JWC
>023 JWC
>024 JWC
>025 JWC
>026 JWC
>027 JWC
>028 JWC
>029 JWC
>030 JWC
>031 JWC
>032 JWC
>033 JWC
>034 JWC
>035 JWC
>036 JWC
>037 JWC
>038 JWC
>039 JWC
>040 JWC
>041 JWC
>042 JWC
>043 JWC
>044 JWC
>045 JWC
>046 JWC
>047 JWC
>048 JWC
>049 JWC
>050 JWC
>051 JWC
>052 JWC
>053 JWC
>054 JWC
>055 JWC
>056 DSO
>057 DSO
>058 DSO
>059 DSO
>060 DSO
>061 DSO
>062 DSO
>063 DSO
>064 DSO
>065 DSO
>066 DSO
>067 DSO
>068 DSO
>069 DSO
>070 DSO
>071 DSO
>072 DSO
>073 DSO
>074 DSO
>075 DSO
>076 DSO
>077 DSO
>078 DSO
>079 DSO
>080 RSL
>081 RSL
>082 RSL
>083 RSL
>084 RSL
>085 RSL
>086 RSL
>087 RSL
>088 RSL
>089 RSL
>090 RSL
>091 RSL
>092 RSL
>093 RSL
>094 RSL
>095 RSL
>096 RSL
>097 DSO
>098 DSO
>099 DSO
>100 DSO
>101 JWC
>102 JWC
>103 JWC
>104 JWC
>105 JWC
>106 JWC
>107 JWC
>108 JWC
>109 JWC
>110 JWC
>111 JWC
>112 JWC
>113 JWC
>114 JWC
>115 JWC
>116 JWC
>117 JWC
>118 JWC
>119 JWC
>120 JWC
>121 JWC
>122 JWC
>123 JWC
>124 JWC
>125 JWC
>126 JWC
>127 JWC
>128 JWC
>129 JWC
>130 JWC
>131 JWC
>132 JWC
>133 JWC
>134 JWC
>135 JWC
>136 JWC
>137 JWC
>138 JWC
>139 JWC
>140 JWC
>141 JWC
>142 JWC
>143 JWC
>144 JWC
>145 JWC
>146 JWC
>147 JWC
>148 JWC
>149 JWC
>150 JWC
>151 JWC
>152 JWC
>153 JWC
>154 JWC
>155 JWC
>156 JWC
>157 JWC
>158 JWC
>159 JWC
>160 JWC
>161 JWC
>162 JWC
>163 JWC
>164 JWC
>165 JWC
>166 JWC
>167 JWC
>168 JWC
>169 JWC
>170 JWC
>171 JWC
>172 JWC
>173 JWC
>174 JWC
>175 JWC
>176 JWC
>177 JWC
>178 JWC
>179 JWC
>180 JWC
>181 JWC
>182 JWC
>183 JWC
>184 JWC
>185 JWC
>186 JWC
>187 JWC
>188 JWC
>189 JWC
>190 JWC
>191 JWC
>192 JWC
>193 JWC
>194 JWC
>195 JWC
>196 JWC
>197 JWC
>198 JWC
>199 JWC
>200 JWC
>201 JWC
>202 JWC
>
>I am attempting to return rows of data that look like this:
>
>READER START END
>JWC 001 055
>DSO 056 079
>RSL 080 096
>DSO 097 100
>JWC 101 202
>
>
>
>The table contains multiple SAMPLEIDs that contain SPECIMENIDs as
>shown above. These records are all READING_NUMBER 1 where these
>specimens have been read the first time. The table will contain Read
>Numbers greater than 1 so I will eventually have to state where
>reading number is 1 and reading number 2 does not exist for that
>specimen id. I can handle that much I just am not sure how to write
>the SQL to get the results as above. I believe if I can compare the
>specimen id to the rownum when returning just one sample_id I may be
>able to get the results I am looking for. Unfortunately the specimen
>id is a text field and comparing it to the rownum may require a data
>type conversion. I am playing with it now.
>
>I want to thank you for your help as it has been very much
>appreciated. If you have any additional ideas with the extra
>information I have provided please let me know. I am working with the
>SQL you have provided as it has given me a few ideas. Thank you very
>much!!!
>
>
>Sincerely,
>
>
>Tim Frawley

----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==---- http://www.newsfeed.com The #1 Newsgroup Service in the World! >100,000 Newsgroups ---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =--- Received on Mon Apr 28 2003 - 12:14:59 CDT

Original text of this message

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