Solved

Distinct with many columns - Continue

Posted on 2008-06-14
6
222 Views
Last Modified: 2010-04-21
I was too quick to accept the solution. If I do as suggested (see below). Then I only get value from s.Col1, but not from the 2 other columns. How can I get the value from all the columns?
Suggested solution:
Select s.Col1, Max(s.Col2), Max(t.Col3)
FROM TestTable t
JOIN SecondTable s
ON s.Col2=t.Col2
WHERE t.ProjectId=@ProjectId
GROUP BY s.Col1


I have a SPROC that I use to select from 3 columns. I want to get all columns returned, but only with distinct Col1 rows.  The sproc below does however not return distinct rows. What can I do?

The SPROC goes something like this:
Select DISTINCT s.Col1, s.Col2, t.Col3 FROM TestTable t
JOIN SecondTable s
ON s.Col2=t.Col2
WHERE t.ProjectId=@ProjectId

0
Comment
Question by:johnkainn
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21785211
you need to clarify, in case you have 2 or more rows for 1 value of Col1, which rows/values have to be returned? like "first/last" row (in which order?), max/min values etc ?
0
 

Author Comment

by:johnkainn
ID: 21785329
How can I do that if Col1 is a text (nvarchar)? The other 2 columns are int.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21785335
please show data samples of your data, and expected output, best with plain english explanation about the output.
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:johnkainn
ID: 21785385
I leave out Col3 as it is not important for this case.
Table data:
Col1   Row1:   "Name1"     Row2: "Name2"    Row3:  "Name1"  
Col2:  Row1:      1              Row2: 2          Row3: 1
Output:
Row1:  a) "Name1"    b)  1
Row2:  b) "Name2"    b) 2
Col2 alway refers to Col1. It is its Id. I would only like to have each "Name" appear once in Output.
Thanks
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 125 total points
ID: 21785425
if the col1 and col2 are always the same, then just GROUP BY the 2 values:

Select s.Col1, s.Col2, Max(t.Col3)
FROM TestTable t
JOIN SecondTable s
ON s.Col2=t.Col2
WHERE t.ProjectId=@ProjectId
GROUP BY s.Col1, s.col2

Open in new window

0
 

Author Closing Comment

by:johnkainn
ID: 31467179
Thank you:-)
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question