Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
BendJoe
Posting Yak Master
128 Posts |
 Posted - 2007-10-16 : 11:38:30
|
| I have a select query Select distinct a,b,c,d from xyzI would like to know what the syntax is if I want only a,b,c to be distinct and not d.I tried something like Select (distinct a,b,c),dbut getting error what is the correct query to do this.Please help. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-10-16 : 11:41:03
|
SELECT A, B, C, MIN(d)FROM Table1GROUP BY A, B, C
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
BendJoe
Posting Yak Master
128 Posts |
Posted - 2007-10-16 : 11:47:31
|
| You are telling me to use MIN function and dont use DISTINCT. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-16 : 11:57:26
|
If you use DISTINCT then you will see EVERY value of [d]So if you want the Distinct values of A, B and C then you need to decide on a single value for [d]. You can have Min, Max or Average  easily. Or you could have a random value of [d], or the first one ordered by some column - such as the date the record was created.With some more effort you can have a list of values (i.e. a comma separate list all-in-the-one-result-column)Kristen |
|
|
|
BendJoe
Posting Yak Master
128 Posts |
Posted - 2007-10-16 : 12:00:44
|
| Yes the solution looked a bit strange but it worked.Thanks a lot. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
BendJoe
Posting Yak Master
128 Posts |
Posted - 2007-10-16 : 13:24:31
|
| Yes the above solution will only return a single value for D.Here is my SQL Select.SELECT distinct aspnet_Membership.LoweredEmail, aspnet_Membership.IsApproved, aspnet_Users.UserName, BuildingAddress.BuildingAddress, UserContact.FirstName, UserContact.LastName, UserContact.CompanyFROM UserContact INNER JOIN BuildingAddress ON UserContact.UserID = BuildingAddress.UserID INNER JOIN aspnet_Membership INNER JOIN aspnet_Users ON aspnet_Membership.UserId = aspnet_Users.UserId ON UserContact.UserID = aspnet_Users.UserIdWHERE (aspnet_Membership.IsApproved = 1)AND((UserName LIKE @UserName) OR (@UserName IS NULL))AND((Company LIKE @Company)OR (@Company IS NULL))AND((BuildingAddress LIKE @Building) OR (@Building IS NULL))Here I have four tables aspnet_Membership,aspnet_Users,UserContact,BuildingAddress all these tables can be connected by a join on UserID. The table named BuildingAddress can have upto three records for a unique UserID. So my query is returning 3 records for each userID provided the BuildingAddress are all different for the same UserID. I need the Query not to give me the record if the BuildingAddress.BuildingAddress is empty. Or I need only distinct and not null BuildingAddress. How can this be done? |
|
|
|
|
|
|
|
|
Select Distinct