Jump to content
WagnerDk

Count records since one of these numbers has been taken.

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.
 

Share this post


Link to post
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 |
+-------+

 

Share this post


Link to post
Share on other sites

No, but maybe a timestamp would be in order. I used id to se which record was newest, but timestamp is better.

1000 thanks to you.

Share this post


Link to post
Share on other sites

 Tell tell us about the real problem you're trying to solve instead of your attempt at solving it.  What are these numbers about?

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×

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.