johnkainn
asked on
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
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
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
Guy Hengel [angelIII / a3]
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 ?
ASKER
How can I do that if Col1 is a text (nvarchar)? The other 2 columns are int.
please show data samples of your data, and expected output, best with plain english explanation about the output.
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
Thank you:-)