| Author |
 Topic  |
|
|
maevr
Posting Yak Master
Sweden
168 Posts |
 Posted - 11/11/2010 : 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 - 11/11/2010 : 02:52:17
|
select distinct myType, myValue from #tbl1
This should return you the unique myType and myValue.
|
 |
|
|
maevr
Posting Yak Master
Sweden
168 Posts |
Posted - 11/11/2010 : 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 - 11/11/2010 : 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 - 11/11/2010 : 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
Sweden
168 Posts |
Posted - 11/11/2010 : 03:43:51
|
Thanks!
The funny thing is that I wrote nearly exactly the same but got different output. |
|
|
| |
Topic |
|
|
|
One per row
