Selecting rows with repeating values in one column

Question

In SQL Server 2008 R2 using this table:

CREATE TABLE mytest(
     id [bigint] IDENTITY(1,1) NOT NULL,
     code [bigint] NULL,
     sequence_number [int] NULL
);

This query:

SELECT id, code, sequence_number
  FROM mytable

returns this data:

 id     code    sequence_number
  1      381        0
  2      381        1
  3      382        0
  4      382        1
  5      383        0
  6      383        1
  7      383        1
  8      384        0
  9      384        1
  10     385        0
  11     385        1
  12     385        2
  13     386        0
  14     386        1
  15     386        1
  16     386        2
  17     387        0
  18     387        1
  19     387        1
  20     387        2
  21     387        3
  22     387        3
  23     388        0
  24     388        1
  25     388        1
  26     388        2
  27     388        2
  28     389        0
  29     389        1

How do I SELECT just these rows:

These are the the MAX(id) rows where there is more than one record with the same sequence_number. I want all the highest ids for each unique combination of code and sequence_number.

So, in effect I want to select just the "duplicate" sequence_number records.

How do I do this?

have you tried my ans?

vikas
– vikas

2013-05-30 12:10:58 +00:00
Commented May 30, 2013 at 12:10 — vikas
– vikas, Commented May 30, 2013 at 12:10

praveen · Accepted Answer · 2013-05-30 11:42:52Z

2

Try this :-

 Select ID,code,sequence_number from
 (
  Select ID,
         code,
         [rn] = row_number() over (partition by code,sequence_number order by id),
         sequence_number
  from yourTable
 )s
 where rn > 1

Demo in SQL FIDDLE

answered May 30, 2013 at 11:42

praveen

12.3k1 gold badge44 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

vikas · Accepted Answer · 2013-05-30 12:03:13Z

1

use this

SELECT 
    MAX(t1.Id) as ID, 
    t1.code, 
    t1.sequence_number
FROM mytest t1
INNER JOIN mytest t2
ON t1.id <> t2.id
AND t1.code = t2.code
AND t1.sequence_number = t2.sequence_number
GROUP BY t1.code, 
    t1.sequence_number
    ORDER BY ID

answered May 30, 2013 at 12:03

vikas

2,8224 gold badges29 silver badges37 bronze badges

Comments

Kartik Gaur · Accepted Answer · 2013-05-30 11:39:49Z

1

select code,MAX(sequence_number) from mytest group by code

answered May 30, 2013 at 11:39

Kartik Gaur

335 bronze badges

Collectives™ on Stack Overflow

Selecting rows with repeating values in one column

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related