Solved

Distinct with many columns - Continue

Posted on 2008-06-14
6
200 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
  • 3
  • 3
6 Comments
 
LVL 142

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 142

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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

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 142

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

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.

Question has a verified solution.

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

Suggested Solutions

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 This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

896 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now