CODEWITHSUNDEEP
sqlgaps-and-islands
beginneeeeer2021
beginneeeeer2021

Reputation: 41

Selecting records that appear several times in a row

My problem is that I would like to select some records which appears in a row. For example we have table like this:

x
x
x
y
y
x
x
y

Query should give answer like this:

x   3
y   2
x   2
y   1

Upvotes: 0

Views: 119

Answers (3)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656942

The elephant in the room is the missing column(s) to establish the order of rows.

SELECT col1, count(*)
FROM  (
   SELECT col1, order_column
        , row_number() OVER (ORDER BY order_column)
        - row_number() OVER (PARTITION BY col1 ORDER BY order_column) AS grp
   FROM tbl
   ) t
GROUP  BY col1, grp
ORDER  BY min(order_column);

To exclude partitions with only a single row, add a HAVING clause:

SELECT col1, count(*)
FROM  (
   SELECT col1, order_column
        , row_number() OVER (ORDER BY order_column)
        - row_number() OVER (PARTITION BY col1 ORDER BY order_column) AS grp
   FROM tbl
   ) t
GROUP  BY col1, grp
HAVING count(*) > 1
ORDER  BY min(order_column);

db<>fiddle here

Add a final ORDER BY to maintain original order (and a meaningful result). You may want to add a column like min(order_column) as well.

Related:

Upvotes: 0

KyleUp
KyleUp

Reputation: 1703

I made a SQL Fiddle http://sqlfiddle.com/#!18/f8900/5

CREATE TABLE [dbo].[SomeTable](
    [data] [nchar](1) NULL,
    [id] [int] IDENTITY(1,1) NOT NULL
);

INSERT INTO SomeTable
    ([data])
VALUES
    ('x'),
    ('x'),
    ('x'),
    ('y'),
    ('y'),
    ('x'),
    ('x'),
    ('y')
;

select * from SomeTable;

WITH SomeTable_CTE (Data, total, BaseId, NextId)
AS
(
SELECT 
    Data,
    1 as total,
    Id as BaseId,
    Id+1 as NextId
    FROM SomeTable
        where not exists(
            Select * from SomeTable Previous
            where Previous.Id+1 = SomeTable.Id 
            and Previous.Data = SomeTable.Data)
UNION ALL
select SomeTable_CTE.Data, SomeTable_CTE.total+1, SomeTable_CTE.BaseId as BaseId, SomeTable.Id+1 as NextId
from SomeTable_CTE inner join SomeTable on
SomeTable.Data = SomeTable_CTE.Data
and 
SomeTable.Id = SomeTable_CTE.NextId
)
SELECT Data, max(total)   as total
FROM SomeTable_CTE
group by Data, BaseId
order by BaseId

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269933

SQL tables represent unordered sets. Your question only makes sense if there is a column that specifies the ordering. If so, you can use the difference-of-row-numbers to determine the groups and then aggregate:

select col1, count(*)
from (select t.*,
             row_number() over (order by <ordering col>) as seqnum,
             row_number() over (partition by col1 order by <ordering col>) as seqnum_2
      from t
     ) t
group by col1, (seqnum - seqnum_2)

Upvotes: 1

Related Questions