[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 241
  • Last Modified:

Distinct with many columns - Continue

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
johnkainn
Asked:
johnkainn
  • 3
  • 3
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
johnkainnAuthor Commented:
How can I do that if Col1 is a text (nvarchar)? The other 2 columns are int.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please show data samples of your data, and expected output, best with plain english explanation about the output.
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
johnkainnAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
johnkainnAuthor Commented:
Thank you:-)
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now