-1

I have some data. I want to count most consecutive duplicate data in MySQL. Please help me.

enter image description here

      id        |    data
----------+----------------
2104            |     B
2938            |     B
3545            |     B
4240            |     B
9879            |     A
9995            |     A
9996            |     B
10107           |     B
10563           |     B
11441           |     B
20974           |     A
20975           |     A
23065           |     A
23066           |     A
47469           |     A
47470           |     A
47471           |     A
62091           |     A
62092           |     A      
----------------+-----------------------

I look only A. this answer is 9

|    data          |   count
+-------------------------------
|    A             |    9               
+------------------------------
Fear555
  • 9
  • 3
  • But there are 11 'A's in your input table? – SMA Oct 02 '19 at 10:04
  • Possible duplicate of [Find duplicate records in MySQL](https://stackoverflow.com/questions/854128/find-duplicate-records-in-mysql) – party-ring Oct 02 '19 at 10:05
  • yes. but i count most data Arrange – Fear555 Oct 02 '19 at 10:06
  • See: [Why should I provide an MCRE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query) – Strawberry Oct 02 '19 at 10:19

1 Answers1

1

Start by getting a count of the various items

 SELECT A, COUNT(*) `count`
   FROM tbl
  GROUP BY A

Then sort by count and take the first row.

 SELECT A, COUNT(*) `count`
   FROM tbl
  GROUP BY A
  ORDER BY COUNT(*) DESC
  LIMIT 1

That is easy enough to make me guess there is more to your requirement, however. Please edit your question.

O. Jones
  • 103,626
  • 17
  • 118
  • 172