don't return row with duplicate value in one colum

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)

don't return row with duplicate value in one colum

Author

Topic

__madmax__
Starting Member

31 Posts

Posted - 2005-10-05 : 04:58:01

Ok, here's what i am struggeling with.

I have a table like this;


CREATE TABLE [dbo].[myTable] (
	[id] [int] IDENTITY (1, 1) NOT NULL ,
	[catId] [int] NULL ,
	[uid] [int] NULL ,
	[amount] [money] NULL 
) ON [PRIMARY]
GO

CatId holds the different category Id's
uid holds the userId's from another table
amount holds different sorts of amounts

let's say you insert the following data


1) insert into myTable(catId, uid, amount) VALUES(1,1,'1.20')
2) insert into myTable(catId, uid, amount) VALUES(1,2,'1.20')
3) insert into myTable(catId, uid, amount) VALUES(1,3,'1.21')
4) insert into myTable(catId, uid, amount) VALUES(1,4,'1.43')
5) insert into myTable(catId, uid, amount) VALUES(1,5,'1.45')
6) insert into myTable(catId, uid, amount) VALUES(1,6,'1.45')

What i like to get back is only the row, where col amount is unique (in this case that would be row 3 and 4) WITH the user ID.

I came up with a first thing like;


select amount, count(*)
from myTable
where catId = 1
group by amount
having count(*) < 2

But that doesn't give me the user Id of the row. If i throw in the uid in this group/having select the rows get unique again.

Hope somebody can help me out here :)

thnxs in advance!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-05 : 05:13:23

Try this


Select T1.uid,T2.amount from 
	mytable T1 inner join 
	(select amount, count(*) as counting from myTable where catId = 1 group by amount having count(*) < 2) T2
on T1.amount=T2.amount

Madhivanan

Failing to plan is Planning to fail

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-10-05 : 05:18:18

select
a.*
from
mytable a
inner join
(select
amount,
count(*) as ct
from mytable
group by amount
having count(*) = 1) b
on a.amount = b.amount

Duane.

__madmax__
Starting Member

31 Posts

Posted - 2005-10-05 : 05:25:52

cool!

thnxs for the quick reply!

joining on the amount, of course !!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources