Re: Complex crosstab query

Comments embedded.
On Sep 26, 5:09 pm, Timo <tim.fraw..._at_alaska.gov> wrote:
> 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.

> 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

> 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?

> 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.

> 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.

> 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.

> 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.

> 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

> 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.

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

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

> Maybe Senior Oracle DumB Ass can...

David Fitzjarrell Received on Mon Sep 29 2008 - 12:04:30 CDT