SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 One per row
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

maevr
Posting Yak Master

Sweden
169 Posts

Posted - 11/11/2010 :  02:44:41  Show Profile  Reply with Quote
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  Show Profile  Visit EugeneLim11's Homepage  Reply with Quote
select distinct myType, myValue from #tbl1

This should return you the unique myType and myValue.

Go to Top of Page

maevr
Posting Yak Master

Sweden
169 Posts

Posted - 11/11/2010 :  03:07:02  Show Profile  Reply with Quote
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.
Go to Top of Page

EugeneLim11
Posting Yak Master

167 Posts

Posted - 11/11/2010 :  03:12:58  Show Profile  Visit EugeneLim11's Homepage  Reply with Quote
how do the computer tell if it ID 1 or 9 you want when there are duplicates? do you select the smallest ID ?
Go to Top of Page

EugeneLim11
Posting Yak Master

167 Posts

Posted - 11/11/2010 :  03:15:18  Show Profile  Visit EugeneLim11's Homepage  Reply with Quote
Select MIN(ID), myType, myValue
FROM #tbl1
group by myType, myValue

should return you the smallest ID with unique myType and myValue.
Go to Top of Page

maevr
Posting Yak Master

Sweden
169 Posts

Posted - 11/11/2010 :  03:43:51  Show Profile  Reply with Quote
Thanks!
The funny thing is that I wrote nearly exactly the same but got different output.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000