Need help

Question

Need help

sachince61

Old Hand

Points: 309
More actions
May 4, 2015 at 11:59 am

#303592

Hi,
I am having table with below data.
create table #temp
(id int, name varchar(10), name1 varchar(10))
insert into #temp
select 1,'a1','sach'
union
select 2,'a2','rock'
union
select 3,'a3','rock'
union
select 4,'a4','sam'
I need output,
'a2'
'a3'
because the column name1 having duplicate value.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

your sample data didn't have any duplicates; since this seems a little homework-like, i'll give you just a couple of hints.

duplicates are typically removed by using a GROUP BY or sometimes a DISTINCT command. (maybe combined with WHERE name='rock'?)

there are occasions where you are not really looking to use GROUP BY because there is related data to what you like for kind-of-group-by field, so you might opt to use ROW_NUMBER() OVER (Partition by{someotherfield} ORDER BY {somefield}, and only select the first instance of the row number in a sub select.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

sachince61 Old Hand Points: 309 More actions · Answer 2

Thanks for the reply.

There is a column name1, in it the rock value is duplicate,

So i need output of the name column.

Thanks in advanced.

Luis Cazares SSC Guru Points: 183698 More actions · Answer 3

Lowell (5/4/2015)
your sample data didn't have any duplicates; since this seems a little homework-like, i'll give you just a couple of hints.
duplicates are typically removed by using a GROUP BY or sometimes a DISTINCT command. (maybe combined with WHERE name='rock'?)
there are occasions where you are not really looking to use GROUP BY because there is related data to what you like for kind-of-group-by field, so you might opt to use ROW_NUMBER() OVER (Partition by{someotherfield} ORDER BY {somefield}, and only select the first instance of the row number in a sub select.

Actually, I wouldn't use ROW_NUMBER(). I'd prefer to use COUNT(*), either using HAVING or OVER(). It seems that a CTE or subquery is needed for this.

I have 2 solutions but I might not post them until the OP shows what has (s)he tried.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Luis Cazares (5/4/2015)
Lowell (5/4/2015)
your sample data didn't have any duplicates; since this seems a little homework-like, i'll give you just a couple of hints.
duplicates are typically removed by using a GROUP BY or sometimes a DISTINCT command. (maybe combined with WHERE name='rock'?)
there are occasions where you are not really looking to use GROUP BY because there is related data to what you like for kind-of-group-by field, so you might opt to use ROW_NUMBER() OVER (Partition by{someotherfield} ORDER BY {somefield}, and only select the first instance of the row number in a sub select.
Actually, I wouldn't use ROW_NUMBER(). I'd prefer to use COUNT(*), either using HAVING or OVER(). It seems that a CTE or subquery is needed for this.
I have 2 solutions but I might not post them until the OP shows what has (s)he tried.

I'd use the OVER version based on the result set requested.