Re: selecting distinct

On Tue, 31 Aug 1999 08:00:16 -0400, Kenneth C Stahl <BluesSax_at_Unforgettable.com> wrote:

>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.
>
>

This statement will actually only return one row. I think the suggestions of using min or max on the name column is what Mark is after, even though it does seem rather strange that one customer number would have more than one name associated with it and the criteria for which name he wants returned are unclear.

-Jeff Received on Tue Aug 31 1999 - 08:43:21 CDT