Need help

• sachince61

  Old Hand

  Points: 329

  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.

• Lowell

  SSC Guru

  Points: 323518

  More actions

  May 4, 2015 at 12:09 pm

  #1795434

  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: 329

  More actions

  May 4, 2015 at 12:18 pm

  #1795438

  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: 183706

  More actions

  May 4, 2015 at 12:30 pm

  #1795440

  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

  May 4, 2015 at 1:43 pm

  #1795459

  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.