CODEWITHSUNDEEP
phpmysqlsqldatabaseselect
Joost
Joost

Reputation: 71

SQL: How can I find repeated value's till separation

What I want to do with SQL is find repeating values in a column until there's an other value sorted by date.

This is my table. I use "CURRENT_TIMESTAMP" for the date column.

Name    |date     
-------------------
Bart    |12-12-2014
Bart    |23-12-2014
Joost   |24-12-2014
Bart    |25-12-2014
Bart    |26-12-2014
Bart    |27-12-2014

So in this example I want the number "3" returned and the last known value of name, so in this case "Bart".

I hope I made myself clear, sorry for the unclear title!

Upvotes: 1

Views: 64

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270583

Hmmm . . . Here is one method where the logic is placed in the where clause:

select count(*), max(lastname.name)
from tablename t cross join
     (select t2.name from tablename t2 order by date desc limit 1) as lastname
where t.name = lastname.name and
      t.date > (select max(t2.date) from tablename t2 where t2.name <> lastname.name);

Upvotes: 4

Giorgos Betsos
Giorgos Betsos

Reputation: 72185

You can use the following query:

SELECT Name, COUNT(*) AS cnt
FROM (
SELECT Name, [date],
       ROW_NUMBER() OVER (ORDER BY [date]) -
       ROW_NUMBER() OVER (PARTITION BY Name ORDER BY [date]) AS grp       
FROM mytable ) AS t
GROUP BY Name, grp
ORDER BY COUNT(*) DESC

This query tries to identify islands of records, i.e. consecutive rows having the same Name: grp calculated field does exactly this.

If you want the Name having the largest number of consecutive records, just use TOP 1 in the above query.

Demo here

Upvotes: 2

Related Questions