Select specialy grouped - 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 2005 Forums

Transact-SQL (2005)

Select specialy grouped

Author

Topic

Shihan
Starting Member

2 Posts

Posted - 2008-11-04 : 05:31:46

Hi,

I have a table, eg. like this:


| No | Name | Desc |

Id has entries like this


| 01 | Test | Blabla |
| 02 | Abc  | Omega |
| 02 | Abc  | Gamma |
| 03 | XYZ  | Alpha |
| 03 | KLM  | Beta  |

As you see, there are some duplicates for the Number. Want I want have, is an output like this:


| 01 | Test |
| 02 | Abc  |
| 03 | XYZ  |

I only want every number to be one time in the result. I really have no further ideas how to achive this.

I cannot simply group it:

SELECT no, name FROM table GROUP BY no, name

This gives me of cours multiple entries for every different name there is per number, but name has to be in the result so I have to include it in the group by. It's not so important which "name" value is taken, to I can live with "select top 1" but I didn't get anything working..

Any ideas how to achive this?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-04 : 05:52:10

Try

SELECT no, min(name) as name FROM table GROUP BY no

Madhivanan

Failing to plan is Planning to fail

raky
Aged Yak Warrior

767 Posts

Posted - 2008-11-04 : 06:07:54

declare @test table ( no varchar(16), name varchar(40), description varchar(32))
insert into @test
select '01', 'Test', 'Blabla' union all
select '02','Abc','Omega' Union all
select '02','Abc','Gamma' Union all
select '03','XYZ','Alpha' Union all
select '03','KLM','Beta'

select
no,
name
from
( select row_number() over ( partition by no order by no ) as id ,no, name from @test ) t
where t.id = 1

Shihan
Starting Member

2 Posts

Posted - 2008-11-04 : 06:24:25

Thanks! Both solutions work perfectly.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources