SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Using DISTINCT with multiple columns?
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

brendita
Starting Member

38 Posts

Posted - 12/09/2005 :  11:39:19  Show Profile
This query returns part of what I need:

SELECT DISTINCT ci.Complex_Name
FROM CaseInformation c
INNER JOIN CommunityInformation ci
ON c.complex_ID = ci.Complex_ID
WHERE c.Date_entered BETWEEN '11/01/2005' AND '12/01/2005'

But I also need to return the address, city, etc for each of those Communities. But when I add those to the query, it returns multiple rows. How can I return more information without getting multiple rows for each distinct Community?

Thanks for your time!

jhermiz
Flowing Fount of Yak Knowledge

USA
3564 Posts

Posted - 12/09/2005 :  11:46:07  Show Profile  Visit jhermiz's Homepage
You can't...the query is doing exactly what you are telling it to do, distinct works on a row not a column basis.
Someone asked the same question yesterday. The reason it returns multiple rows is because the COMPLETE row is NOT distinct, adding those other fields makes it UNIQUE.

What you want to read over is GROUP BY vs. DISTINCT as well as aggregate functions.



Keeping the web experience alive -- http://www.web-impulse.com
Go to Top of Page

brendita
Starting Member

38 Posts

Posted - 12/09/2005 :  13:00:44  Show Profile
I've been looking for some articles GROUP BY vs. DISTINCT but can't find any that solve my problem. Can you lead me to an article?
Go to Top of Page

jhermiz
Flowing Fount of Yak Knowledge

USA
3564 Posts

Posted - 12/09/2005 :  13:40:32  Show Profile  Visit jhermiz's Homepage
But your question all and all doesn't make sense, you want a unique name from case information with multiple rows...

You cant do that because you have a one to many relationship, your problem is not a data problem, it is a problem with presentation. Dont worry about getting multiple rows of the same data as a data issue, worry about it as a presentation issue.

On a report you would create a group by for the case information and then in the details section you would place all your "many side" data. That way you ONLY see ONE Complex_name and all related data.

Sound right ?



Keeping the web experience alive -- http://www.web-impulse.com
Go to Top of Page

uuthanh
Starting Member

3 Posts

Posted - 04/18/2007 :  17:18:29  Show Profile
Brendita,

Did you figure out how to do it? I got similar problem.
I googled all day, but found nothing beside your post.

I tried to extract out: unique customer records from a trouble ticket table. Simple like this:

TicketID (primary)
CustomerID
CustomerName
CustomerAddress
CustomerCity
CustomerState
CustomerZip
CustomerPhone
CustomerRate

If I use DISTINCT then, I can only get back 1 column (CustomerID)
If I use GROUP BY, then I have to group by all the fields above in order to select all. I wish I can GROUP BY only CustomerID, but it let me select all above fields.

How about you, jhermiz? Can you help?

Thanks so much for any clue.
Go to Top of Page

eltombro
Starting Member

United Kingdom
1 Posts

Posted - 04/30/2007 :  11:08:55  Show Profile  Visit eltombro's Homepage  Click to see eltombro's MSN Messenger address
Finally I've found someone with the same problem!

I'm trying the following:

SELECT DISTINCT value1, value2 FROM table WHERE id = ...

Basically I'm trying to retrieve values where 2 columns are distinct. Unfortunately I can't seem to be able to get the second value, value2, and assign it to an ASP variable from my recordset...

Correction: I debugged my query by finally finding out how to run it in Access 2007. Nothing wrong with the query erm in fact it was my subsequent query which was flawed... I'd got the wrong column name for an id column.

To conclude, selecting distinct values will return them all (distinctly) and are assignable as variables from a recordset. Sorry!

Edited by - eltombro on 04/30/2007 12:08:57
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.22 seconds. Powered By: Snitz Forums 2000