Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Help needed with weird select
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
> >
> > --
> > To reply directly, replace the zero in my email address with the
letter > > e.
> >
> >
>
> Try this:
>
> break on first instance of letter
>
> select product id, product name, substr(product name, 1,1)
> first instance of letter
> from myprodtable
> order by product name asc;
>
> The 'break on first instance of letter' will produce the output you
> desire, namely one occurrence of first instance of letter for each
> distinct value in that column. Since that value depends upon the
> product name column, and that column is sorted in ascending order, you
> should return the report as indicated.
>
> --
> David Fitzjarrell
> Oracle Certified DBA
>
>
> Sent via Deja.com
> http://www.deja.com/
Received on Fri Dec 22 2000 - 15:01:46 CST