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 2008 Forums

Transact-SQL (2008)

distinct

Author

Topic

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2013-10-11 : 06:51:35

I need to bring back a distinct list of the name field along with the fields that go with it? How

select	distinct t.id,
			t.name,
			t.description
	from	tblTrkPhase t

Dave
Helixpoint Web Development
http://www.helixpoint.com

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2013-10-11 : 06:59:53

How is this?

SELECT MAX(Id) AS Id, Name, MAX(Description) AS Description
FROM dbo.tblTrkPhase
GROUP BY Name

Dave
Helixpoint Web Development
http://www.helixpoint.com

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-10-11 : 07:14:00

You can use that. But MAX on description column (free text) do not make sense. However if you want to get any of the description for each name, that's fine

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-11 : 08:24:38

distinct list of the name field along with the fields that go with it
then your above query is wrong as you may get values of Id and description from different records.

a much better option would be


SELECT id,name,description
FROM
(
select	ROW_NUMBER() OVER (PARTITION BY t.name ORDER BY t.id DESC) AS RN,
                        t.id,
			t.name,
			t.description
	from	tblTrkPhase t
)t
WHERE RN=1

so that you get all associated values from the same record

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources