aspose file tools*
The moose likes JDBC and the fly likes Using distinct with multiple fields Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Using distinct with multiple fields" Watch "Using distinct with multiple fields" New topic
Author

Using distinct with multiple fields

Marilyn de Queiroz
Sheriff

Joined: Jul 22, 2000
Posts: 9044
    
  10
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?


JavaBeginnersFaq
"Yesterday is history, tomorrow is a mystery, and today is a gift; that's why they call it the present." Eleanor Roosevelt
Scott Johnson
Ranch Hand

Joined: Aug 24, 2005
Posts: 518
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

Joined: Jul 22, 2000
Posts: 9044
    
  10
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
internet detective
Marshal

Joined: May 26, 2003
Posts: 30293
    
150

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.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Scott Johnson
Ranch Hand

Joined: Aug 24, 2005
Posts: 518
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

Joined: Dec 08, 2006
Posts: 106
Yes.. DISTINCT in sql query gives unique row sets..


<a href="http://www.java-tips.org/java-tutorials/tutorials/" target="_blank" rel="nofollow">Java Tutorials</a> | <a href="http://www.planet-java.org" target="_blank" rel="nofollow">Java Weblog</a> | <a href="http://computer-engineering.science-tips.org" target="_blank" rel="nofollow">Computing Articles</a>
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Using distinct with multiple fields