Member Avatar for mavtcr

Earlier I raised a question in this forum regarding the sorting of database
and Reverend Jim answered me as follows. It was Ok

SELECT * FROM mytable ORDER BY fieldname

But then I faced another problem As I mentioned in that I wanted to sort ORDER by 'Tdate' which is a field in my table. It is the transaction date.Worked well, sorted datewise.But the problem is when there are many transactions in the same date,they are not sorted transaction wise,ie.first transaction First,last transaction at the bottom(though Datewise it is perfect).This will affect the calculation.The last transaction is depending on the values of trnsactions just before. In my case when I applied the above code the transactions of the same date appear shuffled therby affecting the calculations
eg:when a Bank Statement is displayed the records should be displayed trnsactionwise, then only the balance will be correct.

I expect my problem is clear....Is there any way out?

Edited by mavtcr
  • 2 Contributors
  • 1 Reply
  • 306 Views
  • 10 Minutes Discussion Span
  • Latest Post Latest Post by pritaeas

Dani AI

Generated

Short answer: the DB will only return rows in a predictable order if your ORDER BY completely defines the sequence. That is why rows that share the same date appear "shuffled" — TDate alone doesn't break ties. was on the right track: you need a deterministic tie‑breaker (a timestamp with time, or a stable transaction sequence).

What to check and fix:

  • Confirm the data type of TDate. If it's a DATE or a text field you lose time-of-day information; store a DATETIME/TIMESTAMP (or add a separate time column) so events on the same day can be ordered.
  • Make sure there is a unique, monotonic sequence for transactions (auto-increment id, sequence number, or precise timestamp). If transaction IDs are stored as text, sort them as numbers (or zero-pad) so lexical order doesn't misplace "10" vs "2".
  • Always include that tie-breaker in your SELECT used for display and for any calculations — do not rely on implicit insertion order.

If you need the running balance calculated on the server (preferred if you want the DB to return a ready-made statement), and you have a modern MySQL server, use a window function to compute the cumulative total in the same deterministic order. Example (MySQL 8+):

SELECT id, tdate, amount,
       SUM(amount) OVER (ORDER BY tdate, id) AS running_balance
FROM transactions
ORDER BY tdate, id;

If you are on an older MySQL version, calculate the balance in your VB code by iterating the resultset returned in the explicit ORDER you specify, or use the user-variable trick in SQL with care (it can be less portable and depends on query shape).

Quick checklist to apply now: confirm TDate type → add/ensure unique sequence → modify retrieval to order by date + sequence → compute running balance using a window function or in your application. This guarantees correct, repeatable transaction order and accurate balances.

Member Avatar for pritaeas

You want this?

SELECT * FROM mytable ORDER BY TDate, transactionid
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.