Re: selecting distinct

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 name
> distinct accountnos and the associated name.
>
> I have to believe there is a sql statement that will accomplish
> this...
> Can anyone help?

You can certainly do this, making use of the GROUP BY, however you are asking for something that (although perhaps practical) is not really logical: which 'name' should it give you for each of the distinct 'customerno' values?

You could do something like (if the min/max 'name' is OK):

select min(name),customerno from mytable group by customerno [or]
select max(name),customerno from mytable group by customerno

Yours,

Geoff Houck
systems hk
hksys_at_teleport.com
http://www.teleport.com/~hksys Received on Tue Aug 31 1999 - 01:39:45 CDT