Selecting records that appear several times in a row

Question

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:

You'll probably need a second column. Maybe an auto-incredmented id column? Also probably easier in excel if you can't get a second column. — KyleUp, Apr 09 '21 at 14:55
Please always declare your database system and version. SQL dialects differ heavily. — Erwin Brandstetter, Apr 09 '21 at 15:26
The last result row `y|1` disagrees with the title. Please clarify. — Erwin Brandstetter, Apr 09 '21 at 17:32

score 1 · Accepted Answer · answered Apr 09 '21 at 14:53

1

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)

answered Apr 09 '21 at 14:53

Gordon Linoff

1,242,037
58
646
786

Sorry, but there must be something wrong - it shows me that: Incorrect syntax near '*'. – beginneeeeer2021 Apr 09 '21 at 15:11
@beginneeeeer2021 . . . The only `*`s in the query are `count(*)` and `t.*`. Both are syntactically correct. – Gordon Linoff Apr 09 '21 at 15:12
One more question - what is t in 2, 6 and 6 line? :) – beginneeeeer2021 Apr 12 '21 at 21:48
@beginneeeeer2021 . . . That is the table name in the `from` clause and a table alias after the parentheses. Your question doesn't specify the table name, so I just use `t`. – Gordon Linoff Apr 12 '21 at 22:11

Erwin Brandstetter · Answer 2 · 2021-04-09T17:28:33.830

0

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.

answered Apr 09 '21 at 15:19

Erwin Brandstetter

605,456
145
1,078
1,228

What is grp here? – beginneeeeer2021 Apr 09 '21 at 15:29
Just alias yes ? – beginneeeeer2021 Apr 09 '21 at 15:29
See `AS grp`? That's the column alias for the computed "group number". Ample explanation in the linked answer. I added another link – Erwin Brandstetter Apr 09 '21 at 15:30
One more question: in 6 and 7th row there should be name of table jest? for example cattable instead od t and tbl? Because I have some error syntax – beginneeeeer2021 Apr 09 '21 at 15:43
`t` in my query is the required alias for the derived table from the subquery. Can be *any* valid identifier. Like `cattable`. `tbl` is a placeholder for the actual name of the underlying table. – Erwin Brandstetter Apr 09 '21 at 15:45
Sorry but it's not what i asked. I was asking: i have table like x x x y y x y y x x y x y. I'm looking for query which gives mi number of names which affected in a row. Here query should give answer: x|3 y|2 y|2 x|2. – beginneeeeer2021 Apr 09 '21 at 16:24
Ah, we can't `ORDER BY grp` as the number is not increasing steadily among different `col1`. Needs to be `ORDER BY min(order_column)` or similar. Fixed & added fiddle. Assuming PostgreSQL as you won't declare. The added example in your comment disagrees with the given example in the question. The question *includes* `y|1` in the result. – Erwin Brandstetter Apr 09 '21 at 17:31

score 0 · Answer 3 · answered Apr 09 '21 at 15:25

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

Selecting records that appear several times in a row

3 Answers3