Extract the highest value of each day

I have a database table that is full of transactions transactionIDs and datetime fields. As you would guess, the datetime field and transactionIDs are constantly increasing and the balance field is either increasing / staying the same / or decreasing.

I would like to 开发者_运维知识库extract the highest transactionID and its corresponding balance at the end of every day.

Thank you in advance for your time and assistance.

Sample Table Format:

transactionID|date (datetime)|amount|balance|

---

SELECT 
  t1.`date`,
  t1.transactionID,
  t2.balance
FROM
  (
    SELECT
      `date`,
      MAX(transactionID) AS `transactionID`
    FROM
      table
    GROUP BY
      DATE(`date`)
  ) t1
INNER JOIN
  table t2
ON
  t2.transactionID = t1.transactionID
ORDER BY
  t1.`date`

---

I suggest you that insert unix_timestamp as your date field on your table.and use this query to fetch heighest transactionID :

$query = 'SELECT MAX (transactionID) FROM [ TABLE NAME ] WHERE [ DATE FIELD NAME ] BETWEEN' .  strtotime("today 23:59"). ' AND ' . strtotime("today")

---

Try this:

SELECT balance FROM table WHERE table.created > TODAY() ORDER BY transactionID DESC LIMIT 1

table.created is the column containing the creation date of the record,

This query should give you the transactionID for just the current day. Do you need it for an interval of days? - in that case, the following query should do it

 SELECT balance FROM 
      (SELECT transactionID, balance FROM table ORDER BY transactionID DESC) 
 GROUP BY DAYOFYEAR(table.created)