CODEWITHSUNDEEP
mysqlsql
Richard Deurwaarder
Richard Deurwaarder

Reputation: 2040

Select last N rows following a condition

I've got a database table with logs which has 3 columns:

date | status | projectId

status can be either 0 or 1, primary key is on date and projectID

I'm trying to find out how many times a projectID had status 0 since the last time it was 1.

so if there would be only one projectId

date | status | projectId
  1       0        3
  2       0        3
  3       1        3
  4       1        3
  5       0        3
  6       0        3

this should return 2 (row 5 and 6 are 0 and row 4 is 1)

The thing that makes it hard for me is that I have to maintain the order of date. What would be a good way to tackle such problems, and this one in particular?

Upvotes: 2

Views: 1261

Answers (3)

spencer7593
spencer7593

Reputation: 108370

Here's one way to get the result for all project_id:

SELECT m.project_id
     , COUNT(1) AS mycount
  FROM ( SELECT l.project_id
              , MAX(l.date) AS latest_date
           FROM mytable l
          WHERE l.status = 1
       ) m
  JOIN mytable t
    ON t.project_id = m.project_id 
   AND t.date > m.latest_date
   AND t.status = 0

If you need only a subset of project_id, the predicate should be added to the WHERE clause in the inline view query:

          WHERE l.status = 1
            AND l.project_id IN (3,5,7)

EDIT

That query does not return a row if there is no status=0 row after the latest status=1 row. To return a zero count, this could be done with an outer join.

SELECT m.project_id
     , COUNT(t.status) AS mycount
  FROM ( SELECT l.project_id
              , MAX(l.date) AS latest_date
           FROM mytable l
          WHERE l.status = 1
            AND l.project_id IN (3)
       ) m
  LEFT
  JOIN mytable t
    ON t.project_id = m.project_id 
   AND t.date > m.latest_date
   AND t.status = 0

For optimum performance, the statement could make use of an index with leading columns of project_id and date (in that order) and including the status column, e.g.

ON mytable (`project_id`,`date`,`status`)

Upvotes: 0

Angelo Saleh
Angelo Saleh

Reputation: 622

here you have an option in just one select.

http://sqlfiddle.com/#!2/6ce87/11

select *
from logs
where status=0 and date > (select date from logs where status=1 order by date desc limit 1)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269463

Here is how you would do it for one project:

select count(*)
from logs l
where status = 0 and
      projectid = 3 and
      date > (select max(date) from logs where projectid = 3 and status = 1)

Here is how you would do it for all projects:

select l.projectId, count(l1.projectId)
from logs l left outer join
     (select projectId, max(date) as maxdate
      from logs
      where status = 1
      group by projectId
     ) l1
     on l.projectId = l1.projectId and
        l.date > l1.date and
        l.status = 0
group by l.projectId;

Upvotes: 4

Related Questions