How to query the index for each record based on timestamp

Question

I have a order table which i would like to index each company's first second third ... etc order.

Example of table:

|---------------------|--------------------------|
|      company_id     |     created_at           |
|---------------------|--------------------------|
|          1          |   2019-01-01 01:00:00    |
|---------------------|--------------------------|
|          1          |   2019-02-01 01:00:00    |
|---------------------|--------------------------|
|          2          |   2019-03-01 02:00:00    |
|---------------------|--------------------------|
|          3          |   2019-03-01 08:30:00    |
|---------------------|--------------------------|
|          2          |   2019-03-01 10:00:00    |
|---------------------|--------------------------|

I Would it be queried like this in the end. but im not sure how..... i keep going back to counting and grouping the fields but i cant seem to get the sum for each row based on the timestamp

|---------------------|--------------------------|-------|
|      company_id     |     created_at           |count()|
|---------------------|--------------------------|--------
|          1          |   2019-01-01 01:00:00    |   1   |
|---------------------|--------------------------|--------
|          1          |   2019-02-01 01:00:00    |   2   |
|---------------------|--------------------------|--------
|          2          |   2019-03-01 02:00:00    |   1   |
|---------------------|--------------------------|--------
|          3          |   2019-03-01 08:30:00    |   1   |
|---------------------|--------------------------|--------
|          2          |   2019-03-01 10:00:00    |   2   |
|---------------------|--------------------------|--------

Possible duplicate of [Row number per group in mysql](https://stackoverflow.com/questions/17939198/row-number-per-group-in-mysql) — Nick, Mar 04 '19 at 03:39

Prahalad Gaggar · Answer 1 · 2019-03-04T05:15:58.057

2

SELECT 
    @row_number:=CASE
        WHEN @company_id = company_id THEN @row_number + 1
        ELSE 1
    END AS count1,
    @company_id:=company_id as company_id,
    created_at
FROM
    Table1,(SELECT @company_id:=0,@row_number:=0) as t
ORDER BY company_id;

SQL Fiddle

edited Mar 04 '19 at 05:15

answered Mar 04 '19 at 05:09

Prahalad Gaggar

11,389
16
53
71

how does @row_number actually work ? im not familiar with using @ in sql – Napmi Mar 04 '19 at 05:42
Napmi : you are initializing a variable @row_number with 0 to start you count for company_id. Just saying: your answer query (for the same question) will be more time consuming – Prahalad Gaggar Mar 04 '19 at 05:45
i believe you are right about the time consuming part. as my order table increases in size , im gonna be screwed by my query. – Napmi Mar 04 '19 at 07:06

score 0 · Answer 2 · edited Mar 04 '19 at 05:43

0

My solution in the end was as below , which seems more simplier than the above has suggested.

SELECT company_id
,(
    SELECT count(*) + 1
    FROM ORDERS AS orderCount
    WHERE orderCount.company_id = orders.company_id
        AND orderCount.created_at < orders.created_at
    ) AS order_count
FROM ORDERS
ORDER BY created_at

edited Mar 04 '19 at 05:43

Prahalad Gaggar

11,389
16
53
71

answered Mar 04 '19 at 05:41

Napmi

521
2
13
32

How to query the index for each record based on timestamp

2 Answers2