Question Using SELECT DISTINCT? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

Question Using SELECT DISTINCT?

Author

Topic

Kervin
Starting Member

12 Posts

Posted - 2005-08-10 : 01:26:16

How can I go about getting a result set that is distinct on only one field and still get the information from the other fields without looping:

If I have a table with
primary_key, first_name,last_name

1,joe,smith
2,bob,smith
3,fred,smith
4,joe,green
5,fred,green

I want to do something like

SELECT (DISTINCT last_name),first_name,primary_key FROM table

I want a way to get the DISTINCT to only look at the last_name field not all fields when filtering the results but still create a record set that contains the last_name, first_name and the primary key?

in the example above I want a results set like this

primary_key, first_name,last_name

1,joe,smith
4,joe,green

thanks.

Thankx

Kervin.
kervin_findlay@hotmail.com

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-10 : 01:30:51

Try this

Select Distinct (select top 1 primary_key from yourtable where last_name=T.last_name),(select top 1 first_name from yourtable where last_name=T.last_name),last_name from yourtable T

Madhivanan

Failing to plan is Planning to fail

Kervin
Starting Member

12 Posts

Posted - 2005-08-10 : 01:58:41

Thank you.

I dont claim to understand all of the logic behind it, but it does exactly what I need. The example I gave was very simplified compared to what I am actually doing but this will help me in a number of situations where I need to use DISTINCT in this way... I hope it doesn't slow down too much on a larger results set - but it does the trick right now. :-)

Thankx

Kervin.
kervin_findlay@hotmail.com

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-10 : 02:13:50

>> hope it doesn't slow down too much on a larger results set

I think it will slow down the performance if there are millions of data

As an alternative try this also

Select * from yourtable
Where primary_key in (select min(primary_key) from yourtable group by last_name)

Madhivanan

Failing to plan is Planning to fail

Kervin
Starting Member

12 Posts

Posted - 2005-08-10 : 02:50:38

That is much cleaner.
Thank you for you help.

Thankx

Kervin.
kervin_findlay@hotmail.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources