Using distinct with multiple fields

I have a select statement like
select
distinct nug.people_id, nug.address
from PERTRPT.notify_user_group nug

I think this will make the combination of people_id and address unique. However, I'm wondering if I can do the select for both fields and only do the distinct on people_id. If so, how would I do that?

What would you like the result set to show? A unique list of people_id's and one of the addresses associated with that id?

If so, a 'group by' clause will give you a single row for each people_id:

select nug.people_id, max(nug.address)
from PERTRPT.notify_user_group nug
group by nug.people_id;

Max(address) probably doesn't make sense. It's just a way of selecting one of the addresses.

I'm thinking that each of the unique people_id's only has one address associated with it, so why make the db sort both columns.

I actually originally was selecting 3 fields, but I no longer need the 3rd field.

I think that if I did
select (distinct *)
I would get every row, right?

Do you think that "group by" is a better way than "distinct" in general because I can't do a "distinct" on just one field?
[ December 05, 2006: Message edited by: Marilyn de Queiroz ]

Originally posted by Marilyn de Queiroz:
I think that if I did
select (distinct *)
I would get every row, right?

Correct.

Do you think that "group by" is a better way than "distinct" in general because I can't do a "distinct" on just one field?

In general, it is preferable to use a standard SQL construct rather than trying to create your own because that is what database vendors optimize for. Don't worry about the extra sort. Sorting 1 row (in each group) takes an almost trivial amount of time.

Based on what you've said, I'd just use the distinct keyword. That's exactly what it's purpose is -- give you a unique set of rows.

Jeanne is right. The additional sort time is insignificant.

Yes.. DISTINCT in sql query gives unique row sets..