Solved

Distinct with many columns - Continue

Posted on 2008-06-14
6
216 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 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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

Technology Partners: 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

Suggested Solutions

Title # Comments Views Activity
SQL Server 2012 express 24 42
Help  needed 3 43
Query group by data in SQL Server - cursor? 3 65
Change this SQL to get all nodes 3 40
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…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

733 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