unique column value, entire row select

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

Transact-SQL (2000)

unique column value, entire row select

Author

Topic

dirwin26
Yak Posting Veteran

81 Posts

Posted - 2005-10-03 : 10:40:45

I am attempting to select rows from a table in order to find unique values in column1 and create a new table:

Column 1 Column 2
1 5
1 10
1 15
1 41
1 22
2 56
2 33

New table after selecting no duplicates within column 1:
Column 1 column 2
1 5
2 56

I guess ultimately I am trying to select the entire row where column 1 experiences a new value and to ignore all other rows where column 1 has duplicate values..

Anyone have any idea how to do this?

Thanks,
Dave

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-03 : 10:42:57

Try this

Select Column1, Column2 from yourTable T
where column2=(select top 1 column2 from yourTable where Column1=T.Column1)

Madhivanan

Failing to plan is Planning to fail

dirwin26
Yak Posting Veteran

81 Posts

Posted - 2005-10-03 : 11:10:35

it looks like that statement is producing duplicate values. It selects the rows where all values in column 2 are the same and as well where all values in column 1 are the same.

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2005-10-03 : 11:56:44

I guess you want min value for column2:

Select column1, min(column2)
from table
group by column1

Take a look at BOL (Books on Line) about group by clause.

dirwin26
Yak Posting Veteran

81 Posts

Posted - 2005-10-03 : 12:00:18

Cool...thanks.

Hippi
Yak Posting Veteran

63 Posts

Posted - 2005-10-03 : 13:33:51

quote:
Originally posted by mmarovic

I guess you want min value for column2:
Select column1, min(column2)
from table
group by column1
Take a look at BOL (Books on Line) about group by clause.

Nope, min doesn't work here.
Hippi

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-04 : 01:53:37

quote:
Originally posted by dirwin26

it looks like that statement is producing duplicate values. It selects the rows where all values in column 2 are the same and as well where all values in column 1 are the same.

What is wrong here?

Declare @t table(column1 int, Column2 int)
insert into @t values(1,5)
insert into @t values(1,10)
insert into @t values(1,15)
insert into @t values(1,41)
insert into @t values(1,22)
insert into @t values(2,56)
insert into @t values(2,33)

Select Column1, Column2 from @t T
where column2=(select top 1 column2 from @t where Column1=T.Column1)

Madhivanan

Failing to plan is Planning to fail

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2005-10-04 : 04:56:19

quote:
Nope, min doesn't work here.

Where? Care to elaborate?

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2005-10-04 : 05:02:09

quote:
What is wrong here?

Maybe he has duplicates on both columns. I mean:

Declare @t table(column1 int, Column2 int)
insert into @t values(1,5)
insert into @t values(1,5)
insert into @t values(1,10)
insert into @t values(1,15)
insert into @t values(1,41)
insert into @t values(1,22)
insert into @t values(2,56)
insert into @t values(2,33)

Select Column1, Column2 from @t T 
where column2=(select top 1 column2 from @t t1 where t1.Column1=T.Column1)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-04 : 05:10:29

>>Maybe he has duplicates on both columns. I mean:

But mine is based on sample data of what questioner posted
In that case my answer would be

Select Distinct Column1, Column2 from @t T
where column2=(select top 1 column2 from @t t1 where t1.Column1=T.Column1)

Madhivanan

Failing to plan is Planning to fail

Hippi
Yak Posting Veteran

63 Posts

Posted - 2005-10-04 : 14:51:44

quote:
Originally posted by mmarovic

quote:
Nope, min doesn't work here.
Where? Care to elaborate?

Look at the expected result first before asking. Is 56<33???

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2005-10-05 : 03:17:28

Ah, ok.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources