Jump to content

Count records since one of these numbers has been taken.


WagnerDk

Recommended Posts

Hey all.

Sorry if my english is a little bad and if my question is a little difficult to understand, then just ask and i will try to make it more understandable. 

I got an table in my db, where on of the columns is a lot of numbers, this column is called "num".
I would like to make some function, that so i would be able to se how many records, that have been made since, some specifik numbers last has been added to the db table.

For example:
The lists of records looks like this 4, 5, 90, 13, 42, 32, 10, 23, 56, 65, 78
In one case i would like to count the records since a number between 70 and 80 has been added. In the example it's 10 record since 78 was added.
In another case, i have to select some numbers, like number 6, 7, 22, 42, 54, 67, and then i would like count the records since one of them has been added. In the example it's been 4 records since 42 was added, which is the last of the 6 numbers i have selected. 

Can someone help me. I have tried to google, but without any luck.
 

Link to comment
Share on other sites

I am going to assume that as well as the "num" column there is also a timestamp column so you know the order the nums were added. EG

mysql> select * from test_A;
+----+------+---------------------+
| id | num  | added               |
+----+------+---------------------+
|  1 |   78 | 2018-10-01 06:00:00 |
|  2 |   65 | 2018-10-01 18:00:00 |
|  3 |   56 | 2018-10-02 06:00:00 |
|  4 |   23 | 2018-10-02 18:00:00 |
|  5 |   10 | 2018-10-03 06:00:00 |
|  6 |   32 | 2018-10-03 18:00:00 |
|  7 |   42 | 2018-10-04 06:00:00 |
|  8 |   13 | 2018-10-04 18:00:00 |
|  9 |   90 | 2018-10-05 06:00:00 |
| 10 |    5 | 2018-10-05 18:00:00 |
| 11 |    4 | 2018-10-06 06:00:00 |
+----+------+---------------------+

Then

mysql> SELECT COUNT(*) as total
    -> FROM test_A ta
    ->      JOIN
    ->      (
    ->          SELECT MAX(added) as latest
    ->          FROM test_A
    ->          WHERE num BETWEEN 70 AND 80
    ->      ) tb
    ->      ON ta.added > latest;
+-------+
| total |
+-------+
|    10 |
+-------+

and

mysql> SELECT COUNT(*) as total
    -> FROM test_A ta
    ->      JOIN
    ->      (
    ->          SELECT MAX(added) as latest
    ->          FROM test_A
    ->          WHERE num IN ( 6, 7, 22, 42, 54, 67 )
    ->      ) tb
    ->      ON ta.added > latest;
+-------+
| total |
+-------+
|     4 |
+-------+

 

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.