One per row - 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

General SQL Server Forums

New to SQL Server Programming

One per row

Author

Topic

maevr
Posting Yak Master

169 Posts

Posted - 2010-11-11 : 02:44:41

I need help writing a query that returns ONE row per combination (no doubles)

output:
One row per myType and myValue

create table #tbl1
(
id int,
myType varchar(10),
myValue varchar(10)
)

insert into #tbl1(myType, myValue) values(1, '1', 'value1')
insert into #tbl1(myType, myValue) values(2, '1', 'value2')
insert into #tbl1(myType, myValue) values(3, '1', 'value3')
insert into #tbl1(myType, myValue) values(4, '1', 'value1')
insert into #tbl1(myType, myValue) values(5, '2', 'value2')
insert into #tbl1(myType, myValue) values(6, '2', 'value1')
insert into #tbl1(myType, myValue) values(7, '2', 'value4')
insert into #tbl1(myType, myValue) values(8, '3', 'value1')
insert into #tbl1(myType, myValue) values(9, '1', 'value1')
insert into #tbl1(myType, myValue) values(10, '2', 'value1')
insert into #tbl1(myType, myValue) values(11, '4', 'value1')

Expected output: One per combination and it's corresponding id
id: myType: myValue:
1 '1' 'value1'
2 '1' 'value2'
3 '1' 'value3'
4 '1' 'value4'
5 '2' 'value1'
6 '2' 'value2'
7 '2' 'value4'
8 '3' 'value1'
11 '4' 'value1'

EugeneLim11
Posting Yak Master

167 Posts

Posted - 2010-11-11 : 02:52:17

select distinct myType, myValue from #tbl1

This should return you the unique myType and myValue.

maevr
Posting Yak Master

169 Posts

Posted - 2010-11-11 : 03:07:02

This does not work since I also want the id in output and then it generates alot more because every row gets to be unique.

EugeneLim11
Posting Yak Master

167 Posts

Posted - 2010-11-11 : 03:12:58

how do the computer tell if it ID 1 or 9 you want when there are duplicates? do you select the smallest ID ?

EugeneLim11
Posting Yak Master

167 Posts

Posted - 2010-11-11 : 03:15:18

Select MIN(ID), myType, myValue
FROM #tbl1
group by myType, myValue

should return you the smallest ID with unique myType and myValue.

maevr
Posting Yak Master

169 Posts

Posted - 2010-11-11 : 03:43:51

Thanks!
The funny thing is that I wrote nearly exactly the same but got different output.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources