Re: selecting distinct

From: Kenneth C Stahl <BluesSax_at_Unforgettable.com>
Date: Tue, 31 Aug 1999 08:00:16 -0400
Message-ID: <37CBC3D0.C8AE2300@Unforgettable.com>

Mark Foley wrote:

> I have a table with the following columns customerno name
> 1234 John
> 1234 Bill
> 1235 Jan
> 1236 Mark . . .
> What I want for a result set is:
> 1234 John
> 1235 Jan
> 1236 Mark . . .
> If I use "select distinct customerno,name from table"
> I still get all of the rows. "select distinct customerno from table"
> gets the unique customer numbers,
> but not the names. This table has thousands of names and account
> numbers. I just want namedistinct accountnos and the associated
> name. I have to believe there is a sql statement that will accomplish
> this...
> Can anyone help?

There is, but you won't like it any better:

select accountnos,name
from mytable
where accountnos in (select distinct accountnos

                                  from mytable)

   and rownum = 1;

Your problem is that you have the same account number associated with two different names. Unless you have a criteria for which name you want it will always end up being a crap shoot as to which one you will get. Received on Tue Aug 31 1999 - 07:00:16 CDT