mysql composite primary key with dates [on hold]

26
February 12, 2019, at 09:00 AM

I have a table with 3 columns

(customer_id (int),time (datetime), balance (double)), 

The table structure has 1 index (composite primary key) with 2 columns (customer_id, time)

I need to get the balances in the latest day of december 2018 that exists in the table

The table have million of records and it takes too much time to get the records

Answer 1

Since you have not provided anything, try the following. The inner joined to query will get each customer and the latest time that is within the 2018 year. Then that is brought back to the original table by customer and time to get the balance based on that latest date record.

select
      YT.customer_id,
      YT.time,
      YT.balance
   from
      YourTable YT
         JOIN ( select
                      customer_id,
                      max( time ) latestTime
                   from
                      YourTable yt1
                   where
                      year( time ) = 2018 
                   group by
                      customer_id ) By2018
            ON YT.customer_id = By2018.customer_id
           AND YT.time = By2018.latestTime

(customer_id (int),time (datetime), balance (double)),

Answer 2

I will suggest, 1. Add Time with the date. Then check this query -> SELECT * FROM YourTableName WHERE time = value ORDER BY DESC; I hope this query is fixed your problem successfully.

READ ALSO
Sql Query to present NULL rows

Sql Query to present NULL rows

I have a simples table:

54
Logging in in PHP while logging data is AES encrypted

Logging in in PHP while logging data is AES encrypted

My SQL table contains two types of data used to log inFirst is email(In table named standard_name) and second is password

63
How to setup a Linux development server using Docker to support multiple versions of PHP and MySQL

How to setup a Linux development server using Docker to support multiple versions of PHP and MySQL

I have many PHP projects, and each project requires different PHP and MySQL versionsI want to setup a Docker machine that runs multiple instances of the server with specific versions of PHP and MySQL

39