Re: Help needed with weird select

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
>
> --=20
> 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/