• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Using distinct with multiple fields

 
Marilyn de Queiroz
Sheriff
Posts: 9065
12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?
 
Scott Johnson
Ranch Hand
Posts: 518
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Marilyn de Queiroz
Sheriff
Posts: 9065
12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ]
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34686
367
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Scott Johnson
Ranch Hand
Posts: 518
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Neerav Narielwala
Ranch Hand
Posts: 106
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes.. DISTINCT in sql query gives unique row sets..
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic