• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 120
  • Last Modified:

SQL select without identical values

Hello

I have a table "MyTable"  with the fields: MyTable.Id, MyTable.Value, MyTable.Text

How can i make a select which returns me all three fields, but not having the same MyTable.Value ?

"SELECT DISTINCT MyTable.Value FROM MyTable" returns only the column "MyTable.Value"
and
"SELECT MyTable.Value from MyTable group by MyTable.Value" also returns only the column "MyTable.Value"

But I need the MyTable.Text also in my selection.....

Regards

Eric

Ps: I have SQL 2008
0
ICSAutomation
Asked:
ICSAutomation
  • 2
1 Solution
 
SimonCommented:
Select min(mytable.id),mytable.value,min(mytable.text)
from mytable
group by mytable.value

Open in new window

or
select MT.* from Mytable MT inner join 
	(SELECT  value
	,row_number() over (partition by VALUE order by ID) as 'rn' 
	from practice
	) dt 
	on mt.value=dt.value
	where rdn=1

Open in new window


You're omitting all but one row for each value of MyTable.Value, so you can control WHICH of the rows is included in the result set by changing the ORDER BY clause of the row_number() function.
0
 
ICSAutomationAuthor Commented:
good and fast!!!!
Thanks!
0
 
rajeevnandanmishraCommented:
Hi Eric,

The requirement itself seems to elaborated a little more.
As per your data:
ID       Value        Text
1          10             ABC
2          10             XYZ

In this data you want all the three fields, but only one distinct value 10. Now you need to tell SQL Server that what you would like to have for [ID] and [Text] columns. Do you want Minimum value? Do you want Maximum value, or May be any other AGGREGATE value?

If you want Minimum value for [ID] and [Text] columns, then the query provided by Simon above is perfect.

But do consider, that minimum[ID] and minimum[Text] may not be picked from the same row. You could end up having [ID] from one row and [Text] from some other row.
0
 
ICSAutomationAuthor Commented:
Hello
Thanks for elaboration!!
Very good explanation!!

Eric
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

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