Re: Help needed with weird select

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 22 Dec 2000 21:09:22 -0000
Message-ID: <977519178.7017.0.nnrp-13.9e984b29@news.demon.co.uk>

It's another option for the analytic functions (8.1.6) I'm making this up as I go along, so I might get it wrong, but you need something like:

select

    product_id, product_name,
    decode(row_pos,1,substr(product_name,1,1)) from

    (
    select

        product_id, product_name, row_number() over (
            partition by substr(product_name,1,1
            order by product_name
        ) row_pos
    from
        product_list

    )
order by product_name;

--
Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Practical Oracle 8i:  Building Efficient Databases

Publishers:  Addison-Wesley
See a first review at:
http://www.ixora.com.au/resources/index.htm#practical_8i
More reviews at: http://www.jlcomp.demon.co.uk/book_rev.html



joebob wrote in message <977518916.951122_at_news.zipcon.net>...
Thanks for your response David.  I don't know if this will work for me
because basically I need to get the data from the output of a select, or a
view.  -joebob

"David Fitzjarrell" <oratune_at_aol.com> wrote in message
news:9209e9$mqj$1_at_nnrp1.deja.com...


> In our last gripping episode "joebob" <joebob_at_zipcon.n0t> wrote:

> > Lets say I have 1000 products and want to return the product_id, =

> > product_name, ordered by product_name ascending, and (here is the

 weird =


> > part) I also want to return the first instance of each letter of the =

> > alphabet from the product_name's first character.  Kind of like this:

> >

> > product_id     product-name     first_instance_of_letter

> > -----------     --------------     -----------------------

> > 1                    apple pie            a

> > 2                    aspirin

> > 3                    coffee                 c

> > 4                    ground beef        g

> > 5                    gum                   =20

> > etc ..

> >

> > How would this be done if it's even possible?  Thanks

> >

Received on Fri Dec 22 2000 - 15:09:22 CST