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

Nick
– Nick

2019-03-04 03:39:12 +00:00
Commented Mar 4, 2019 at 3:39 — Nick
– Nick, Commented Mar 4, 2019 at 3:39

Prahalad Gaggar · Accepted Answer · 2019-03-04 05:15:58Z

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 4, 2019 at 5:15

answered Mar 4, 2019 at 5:09

Prahalad Gaggar

11.6k17 gold badges58 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Napmi Over a year ago

how does @row_number actually work ? im not familiar with using @ in sql

Prahalad Gaggar Over a year ago

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

Napmi Over a year ago

i believe you are right about the time consuming part. as my order table increases in size , im gonna be screwed by my query.

Prahalad Gaggar · Accepted Answer · 2019-03-04 05:43:44Z

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 4, 2019 at 5:43

Prahalad Gaggar

11.6k17 gold badges58 silver badges73 bronze badges

answered Mar 4, 2019 at 5:41

Napmi

5412 gold badges14 silver badges34 bronze badges

Collectives™ on Stack Overflow

How to query the index for each record based on timestamp

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related