Correct syntax for DISTINCT - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Correct syntax for DISTINCT

Author

Topic

cronincoder
Yak Posting Veteran

56 Posts

Posted - 2006-02-12 : 16:00:36

Is it possible to refer to just one column in a SELECT statement using DISTINCT when the select statement refers to many columns?

Example:


SELECT DISTINCT name, number, location
FROM table1

What could I do to this statement to just select distinct name?

thank you

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-12 : 16:53:31

You mean like this ?

select	t.name, t.number, t.location
(
	select distinct name from table1
) as d 
inner join table1 t
on	d.name	= t.name

----------------------------------
'KH'
everything that has a beginning has an end

nr
SQLTeam MVY

12543 Posts

Posted - 2006-02-12 : 18:09:33

Which number, location do you want to select to go with the name if there are duplicates?
I would have thought that name was unique in that table but

select name, number = max(number), location = max(location)
from table1
group by name

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-13 : 01:37:59

Otherwise post some sample data and the result you want

Madhivanan

Failing to plan is Planning to fail

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2006-02-13 : 06:56:23

quote:
Originally posted by khtan

You mean like this ?
select t.name, t.number, t.location
(
select distinct name from table1
) as d
inner join table1 t
on d.name = t.name

----------------------------------
'KH'
everything that has a beginning has an end

That's funny and inefficient way to return the same row set as with:


select name, number, location
from table1

The problem is that it is not clear at all what cronincoder wants to be result of the query (as both nr and madhivanan mentioned)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources