• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Using distinct with multiple fields

 
Sheriff
Posts: 9109
12
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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?
 
Ranch Hand
Posts: 518
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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: 9109
12
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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 ]
 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Ranch Hand
Posts: 106
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Yes.. DISTINCT in sql query gives unique row sets..
 
Don't get me started about those stupid light bulbs.
reply
    Bookmark Topic Watch Topic
  • New Topic