MySQL only considers new records

Question

How to write a MySQL query to complete this task?

Table: Writers

w_id w_name --------------- 1 Michael 2 Samantha 3 John --------------- 

Table: Articles

 a_id w_id timestamp a_name ---------------------------------------- 1 1 1 PHP programming 2 3 3 Other programming languages 3 3 5 Another article 4 2 15 Web design 5 1 20 MySQL ---------------------------------------- 

Only COUNT writers who have published their first article are required. not earlier than 5 .
(only authors who have published at least one article can be considered)

In this example, the result will be: 1 (one author is Samantha)

SQL code can be checked in this SQLFiddle

Answer 1

You need a double SELECT.

First you get w_id along with the label of the first article:

 SELECT w_id, MIN(timestamp) as publish FROM articles GROUP BY w_id 

Then you request to publish no earlier than 5:

 SELECT w_id, MIN(timestamp) as publish FROM articles GROUP BY w_id HAVING publish >= 5; 

You will then join this “table” with writers to get the name if you want.

But if you only need a count, you don't need a writers table at all:

  SELECT COUNT(*) AS answer FROM ( SELECT w_id, MIN(timestamp) AS publish FROM articles GROUP BY w_id HAVING publish >= 5 ) AS counter; 

Test: http://sqlfiddle.com/#!2/0e90f/30/0

Answer 2

 select w_name from writers w join articles a on w.w_id = a.w_id group by w.w_id,w.w_name having min(timestamp)>=5 

with a violin. http://sqlfiddle.com/#!2/0e90f/11/0

or count.

 select count(w_name) from writers w join articles a on w.w_id = a.w_id group by w.w_id,w.w_name having min(timestamp)>=5 

with a violin. http://sqlfiddle.com/#!2/0e90f/27/0

Answer 3

You must have a subquery to get the minimum timestamp record size. The result will be calculated, since you cannot aggregate columns that are already aggregated (e.g. COUNT (MIN (columnName)))

 SELECT COUNT(*) totalWriters FROM ( SELECT a.w_id, MIN(b.`timestamp`) FROM writers a INNER JOIN articles b ON a.w_id = b.w_id GROUP BY a.w_id HAVING MIN(b.`timestamp`) >= 5 ) a 

SQLFiddle Demo

Answer 4

This is easy, here is the query:

 SELECT count(DISTINCT w_id) FROM articles WHERE TIMESTAMP>=5 AND w_id NOT IN (SELECT w_id FROM articles WHERE TIMESTAMP<5); 

See SQL Fiddle

Answer 5

A simple solution:

 SELECT COUNT(DISTINCT w_id) FROM writers WHERE w_id NOT IN (SELECT w_id from articles WHERE timestamp <=5) 

Answer 6

 select count(*) from ( select w_id, min(`timestamp`) as `timestamp` from articles group by w_id ) s where `timestamp` >= 5 

Answer 7

 select count(1) from ( select w.w_id, count(a.timestamp) cnt from writers w inner join articles a on a.w_id = w.w_id group by w.w_id ) t inner join articles art on art.w_id = t.w_id where t.cnt = 1 and art.timestamp > 5 

Answer 8

Try this, I just tested.

 select* from ( select tblw.w_id, count(tblart.timestamp) count from writers tblw inner join articles tblart on tblart.w_id = tblw.w_id group by tblw.w_id ) temp inner join articles art on art.w_id = temp .w_id where temp .count = 1 and art.timestamp > 5