On Sep 11, 2011, at 9:58 PM, Igor Tandetnik wrote:
> Mr. Puneet Kishor <punk.k...@gmail.com> wrote:
>> geo table: 39K rows
>> id max_age min_age
>> --- ------- --------
>> 1 Holocene Holocene
>> 5 Cambrian Silurian
>> 12 Cambrian Ordovician
>> 229 Cretaceous Quaternary
>>
>> intervals table: ~450 rows
>> id age_bottom age_top name color
>> --- ---------- ------- ----------- -------
>> 3 0.0117 0.0000 Holocene #FEF2E0
>> 105 443.7000 416.0000 Silurian #B3E1B6
>> 112 488.3000 443.7000 Ordovician #009270
>> 421 2.5880 0.0000 Quaternary #F9F97F
>> 122 542.0000 488.3000 Cambrian #7FA056
>> 33 145.5000 65.5000 Cretaceous #7FC64E
>>
>> Keep in mind, max_age is older than min_age, and age_bottom is older than
>> age_top.
>>
>> The table geo can also have rows with min_age = max_age. I want a result set
>> with geo.id, min_age, max_age, age_bottom, age_top,
>> name, color like so:
>>
>> - every row should be for one and only one geo record. I have 39K rows in
>> "geo" table, so the result set should have 39K rows.
>>
>> - when min_age = max_age, list the corresponding intervals.name and color
>>
>> - when min_age != max_age, find the interval with the smallest different
>> between age_bottom and age_top that would span min_age
>> and max_age of geo. In other words, the interval whose age_bottom is bigger
>> than the age_bottom of the max_age and whose age_top
>> is smaller than the age_top of the min_age.
>
> I'm not sure I understand. Let's take geo.id = 5, max_age=Cambrian,
> min_age=Silurian. You say you want a record whose age_bottom is greater than
> that corresponding to Cambrian, that is 542.0000; and whose age_top is
> smaller than that corresponding to Silurian, or 416.0000. I don't seem to see
> any such record in your example.
Hi Igor, I muddied the issue by providing limited sample data that doesn't have
an answer that fits my needs. Needless to say, in the complete tables there
would be answers. My (slightly simplified) query below shows the kind of result
I want
SELECT g.id, max_age, min_age, Min(i.age_bottom - i.age_top) age_range,
i.interval_name, i.interval_color
FROM geo g
LEFT JOIN intervals i_max ON g.max_age = i_max.interval_name
LEFT JOIN intervals i_min ON g.min_age = i_min.interval_name
JOIN intervals i ON
i.age_bottom >= i_max.age_bottom AND
i.age_top <= i_min.age_top
GROUP BY g.gid, g.max_age, g.min_age, i.interval_name, i.interval_color
ORDER BY g.gid, age_range;
produces the following ---
g.id max_age min_age age_range interval_name
interval_color
------- --------------- --------------- --------------- ---------------
--------------
1 Paleozoic Paleozoic 291.0000 Paleozoic #99C08D
1 Paleozoic Paleozoic 542.0000 Phanerozoic #9AD9DD
4 Precambrian Precambrian 3458.0000 Precambrian #F04370
5 Cambrian Silurian 291.0000 Paleozoic #99C08D
5 Cambrian Silurian 542.0000 Phanerozoic #9AD9DD
6 Silurian Silurian 27.7000 Silurian #B3E1B6
6 Silurian Silurian 291.0000 Paleozoic #99C08D
..
94K+ rows
What I want from the above table is only the first row of each g.id group
because that has the interval that has the smallest "age_range" that spans the
"max_age" and "min_age"
Sorry, I am not able to articulate this more clearly, but I hope the above
example makes things clearer.
Puneet.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
