hi - 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)

Author

Topic

bhu
Starting Member

1 Post

Posted - 2003-06-04 : 20:07:40

Hi all

i have a scenario like this

Table A:

ID Name Salary

three column where ID is the auto increment and primary key field

The data entered are like this

ID Name Salary

1 aaa 1000
2 aaa 2000
3 aaa 3000
4 bbb 3000

i want the result containing only one row in case if the name is repeated many times

In other words

i like my output to be

1 aaa 1000
4 bbb 3000

how do u go about it?

thanks in advance

chadmat
The Chadinator

1974 Posts

Posted - 2003-06-04 : 20:21:01

use tempdb

create table #t1(ID int, Name varchar(3), Salary int)

insert into #t1 values(1, 'aaa', 1000)
insert into #t1 values(2, 'aaa', 2000)
insert into #t1 values(3, 'aaa', 3000)
insert into #t1 values(4, 'bbb', 3000)

select a.ID, a.Name, a.Salary from #t1 a
join (select min(ID) ID, Name from #t1 group by Name) b
on a.ID=b.ID

-Chad

http://www.clrsoft.com

Software built for the Common Language Runtime.

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-06-04 : 20:21:44

How do decide which of the 3 rows you want to display ?

Damian

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources