Number of consecutive non-null values
The score should be 3 and 1 in the next request. The score must be the points obtained in succession. Therefore, as soon as the user cannot earn any points, the counter is restarted.
mysql> select name, count(*) from sortest group by name, (points = 0) OR (points is NULL) having name= 'john';
+------+----------+
| name | count(*) |
+------+----------+
| john | 4 |
| john | 2 |
+------+----------+
2 rows in set (0.00 sec)
mysql> select * from sortest;
+----+------+--------+
| id | name | points |
+----+------+--------+
| 1 | john | 12 |
| 2 | john | 23 |
| 3 | john | 43 |
| 4 | hari | NULL |
| 5 | hari | 56 |
| 6 | john | NULL |
| 7 | hari | 0 |
| 8 | john | 44 |
| 9 | john | 0 |
| 10 | hari | 43 |
| 11 | hari | 44 |
| 12 | hari | 78 |
| 13 | hari | 0 |
+----+------+--------+
13 rows in set (0.00 sec)
mysql> show create table sortest\G
*************************** 1. row ***************************
Table: sortest
Create Table: CREATE TABLE `sortest` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(100) default NULL,
`points` int(11) default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=14 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
In this case, the number of john should be 5, 1 and 2
5 and 1 because it ends with 0 or NULL and 2 because it hasn't closed with 0 or NULL yet
mysql> select * from sortest;
+----+------+--------+
| id | name | points |
+----+------+--------+
| 1 | john | 12 |
| 2 | john | 23 |
| 3 | john | 43 |
| 4 | hari | NULL |
| 5 | hari | 56 |
| 6 | john | NULL |
| 7 | hari | 0 |
| 8 | john | 44 |
| 9 | john | 0 |
| 10 | hari | 43 |
| 11 | hari | 44 |
| 12 | hari | 78 |
| 13 | hari | 0 |
| 14 | john | 55 |
| 15 | john | 95 |
+----+------+--------+
15 rows in set (0.00 sec)
mysql> select name, count(*) from sortest group by name, (points = 0) OR (points is NULL) having name= 'john';
+------+----------+
| name | count(*) |
+------+----------+
| john | 6 |
| john | 2 |
+------+----------+
2 rows in set (0.00 sec)
source to share
To select the maximum winning streak:
SELECT name, MAX(cnt)
FROM (
SELECT name, COUNT(*) AS cnt
FROM (
SELECT sortest.*,
@r := @r + ((COALESCE(@name, name) <> name) OR (COALESCE(points, 0) = 0)) AS series,
@name := name
FROM (
SELECT @name := NULL,
@r := 0
) vars, sortest
ORDER BY
name, id
) q
WHERE points > 0
GROUP BY
name, series
) q2
GROUP BY
name
To select all winning streaks:
SELECT name, COUNT(*) AS cnt
FROM (
SELECT sortest.*,
@r := @r + ((COALESCE(@name, name) <> name) OR (COALESCE(points, 0) = 0)) AS series,
@name := name
FROM (
SELECT @name := NULL,
@r := 0
) vars, sortest
ORDER BY
name, id
) q
WHERE points > 0
GROUP BY
name, series
source to share
Why should the counters be 3 and 1? Your request groups:
- name
- (dots = 0 OR dots IS NULL)
There are two lines where name=john
and (points = 0 OR points IS NULL)
, and there are four more lines where name=john
.
So your query works the same way as you described it. What are you ultimately trying to accomplish?
source to share
First, what you are asking is a little counterintuitive. You see, there is no semblance of order without a sentence order by
. But, I suppose you want to get a count of everything up to the line null
sorted by id
.
Second, here goes:
select
a.name,
(select count(*) from sortest where id < a.id and
id > b.priorId) as count
from
sortest a
inner join (select id,
(select coalesce(max(id), 0)
from sortest where points is null and id < s1.id) as priorId
from sortest s1 where points is null
union
select max(id) as id,
(select coalesce(max(id), 0)
from sortest where points is null and id < s1.id) as priorId
from sortest s2group by name) b on
a.id = b.id
It's a little messy and confusing, but you get what you want.
source to share