Solved

Distinct with many columns - Continue

Posted on 2008-06-14
6
189 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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

747 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

12 Experts available now in Live!

Get 1:1 Help Now