Complex crosstab query

#21

Comments embedded.
On Sep 26, 5:09*pm, Timo <tim.fraw... (AT) alaska (DOT) gov> wrote:

Quote:

You ASS-ume that I have not already tried anything because I didn’t
post it. *

Given this group and the behaviour of those who post homework to it
that is a valid presumption.

Quote:

Typical of you ASS-umer types is the fact that if all
information isn’t provided up front then the poster deserves a flame
comment regarding their lack of integrity and/or intelligence. *

Asking a question in an intelligent manner isn't difficult:
http://www.catb.org/~esr/faqs/smart-questions.html

Quote:

It is
ASS-umers like you that make most people not want to post on these
forums because of the response we are most likely to get. *

And you've seen the questions which generate such responses and you
insist upon posting in like manner, expecting a different result?

Quote:

Here is an
ASS-umption for you: *For you, Senior DBA should stand for Senor DumB
Ass.

Juvenile retorts won't earn you any respect.

Quote:

As for “volunteering” the only thing you are willing to volunteer is
your arrogant, egotistical vehemence. *I didn’t ask you to do anything
you weren’t willing to do, however; you appear to be more than willing
to spout your arrogant ignorance to everyone on the web.

I'd check again at how you posted your question, then read the link I
provided. At that time you can re-evaluate your comment.

Quote:

Maybe you were just having a bad day because someone stole your
coloring book. *

Again, juvenile behaviour won't garner any respect in this newsgroup.
You should seriously reconsider your tone.

Quote:

I don’t know what your issue is but I sure do know
that I don’t deserve your comments. *

You haven't proven that with this glorious response, nor with your
original question.

Quote:

What a D**K.

The problem I have with that query is figuring out how to get the
group of data that exists between Booleans. *If the guy that put the
table together had thought that through then maybe he would have setup
the table differently. *I sure would have. *How would you

select * from circulus where circulus_number < (select circulus_number
from circulus where THE FIRST ANNULUS IS 1 *and image_name =
'KV0313M030' ) and image_name = 'KV0313M030'

or something like that.

Here I can get the Circulus Number but how can I get it to give only
one value so I can use it as an embedded query?

The answer to that depends upon many things, including how many
circulus_number values are returned by that query, whether you want
the lowest or highest value in that list, whether you simply want ONE
value from that list regardless. Several possibilities exist:

select * from circulus where circulus_number < (select circulus_number
from circulus where THE FIRST ANNULUS IS 1 and image_name =
'KV0313M030' and rownum = 1) and image_name = 'KV0313M030'; <--
subquery returns ONE value

select * from circulus where circulus_number < (select
min(circulus_number)
from circulus where THE FIRST ANNULUS IS 1 and image_name =
'KV0313M030' ) and image_name = 'KV0313M030'; <-- returns LOWEST
value

select * from circulus where circulus_number < (select
max(circulus_number)
from circulus where THE FIRST ANNULUS IS 1 and image_name =
'KV0313M030' ) and image_name = 'KV0313M030'; <--- returns HIGHEST
value

select * from circulus where circulus_number < (select circulus_number
from
(select circulus_number, rownum rn
from circulus where THE FIRST ANNULUS IS 1 and image_name =
'KV0313M030' ) and rn = 13) and image_name = 'KV0313M030'; <--- uses
the 13th value returned in the result set

Quote:

select image_name,circulus_number,first_value(annulus) over (order by
CIRCULUS_NUMBER) FROM CIRCULUS where annulus=1 and
image_name='KV0313M030'

Of course, that would just give me the first number. *What about
between the next two Booleans.

You need to think differently about the result set and how it's
processed. I doubt analytical functions are the route to take here,
but I could be wrong.

Quote:

Uhg, I just don’t know enough about the SQL to figure it out.

That's not a crime. Please see examples provided.

Quote:

Maybe Senior Oracle DumB Ass can...

David Fitzjarrell

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Complex crosstab query

comp.databases.oracle.misc comp.databases.oracle.misc

Re: Complex crosstab query - 09-29-2008 , 12:04 PM