Re: Help needed with weird select

Thanks Jonathan,

I just checked and unfortunately our db is version 8.0 or something between 8.0 and 8.1.6. I tried your example and other analytic function examples from the online docs and I always get this message: "ORA-00923: FROM keyword not found where expected".

Does anyone know of another way I can get the same results? Thanks

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:977519178.7017.0.nnrp-13.9e984b29_at_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
> > > etc ..
> > >
> > > How would this be done if it's even possible? Thanks
> > >
>
>
>
  Received on Fri Dec 29 2000 - 18:24:01 CST