Re: select disinct special

From: Klaus Zeuch <KZeuch_at_nospamhotmail.com>
Date: 2000/08/05
Message-ID: <8mfgah$vkv$15$1@news.t-online.com>#1/1

It's time for the analytical functions of Oracle 8.1.6:

SQL> select ename from (
  2 select ename, row_number() over
  3 (partition by substr(ename,1,1) order by ename) rank   4 from emp) where rank = 1;

ENAME

---

ADAMS
BLAKE
CLARK
FORD
JAMES
KING
MARTIN
SCOTT
TURNER
WARD 10 rows selected.

(table has 14 rows; Allen, Jones, Miller and Smith are skipped over)

Klaus

"TurkBear" <johng_at_mm.com> schrieb im Newsbeitrag news:398afea3.14737301_at_news...
>
> That will work if all you want is the initial letter and not the City
 name....If
> you add the city name to the select statement it will not do want you
 want...
>
>
> Jacek Kobusiñski <jkobusinski_at_cs.put.poznan.pl> wrote:
>
> >Try with function substr
> >
> >ex.
> >select distinct subst(city_name,1,1) from city_table;
> >
> >Kobus
> >
> >
> >
> >Stefan wrote:
> >
> >> Hi,
> >>
> >> got table like
> >>
> >> CITY
> >>
> >> Dallas
> >> Detroit
> >> ...
> >> New York
> >> Nagasaki
> >> ...
> >> Washington
> >> ...
> >>
> >> how to get only one city of each letter ?
> >>
> >> i just wanna get back
> >>
> >> D ( or any city with starting 'D' , but only ONE city)
> >> N ...
> >> W ...
> >>
> >> any ideas out there ?
> >> thanx
>
Received on Sat Aug 05 2000 - 00:00:00 CDT