In a grouped table, how to select an ID of a row that has the MAX(timestamp) within the group?

Question

CREATE TABLE account (
  account_id VARCHAR(36) NOT NULL PRIMARY KEY, -- uuid
  account_name VARCHAR(255) NOT NULL
);

CREATE TABLE `transaction` (
  transaction_id VARCHAR(36) NOT NULL PRIMARY KEY, -- uuid
  transaction_timestamp BIGINT NOT NULL,
  account_debit_id VARCHAR(36) NOT NULL,
  account_credit_id VARCHAR(36) NOT NULL,
  transaction_amount DECIMAL(10,2) NOT NULL,
  FOREIGN KEY (account_debit_id) REFERENCES account(account_id),
  FOREIGN KEY (account_credit_id) REFERENCES account(account_id)
);

CREATE TABLE balance_event (
  balance_event_id VARCHAR(36) NOT NULL PRIMARY KEY, -- uuid
  transaction_id VARCHAR(36) NOT NULL,
  account_id VARCHAR(36) NOT NULL,
  account_balance DECIMAL(10,2) NOT NULL,
  FOREIGN KEY (transaction_id) REFERENCES `transaction`(transaction_id),
  FOREIGN KEY (account_id) REFERENCES account(account_id)
);

balance_event is used to keep track of the balance of every account; the table records the events when the balance of an account is changed as a result of some transaction. This way, getting the account balance doesn't require traversing and summing all the thousands of transactions, and also the history of the account balance is kept.

I want to describe a query that gives me all rows from the account table, together with their corresponding account_balance taken from balance_event. I'm testing the code in an online SQL editor that happens to use MySQL, but I seek a query supported by PostgreSQL.

I've tried many different approaches, none of which work for different reasons. Some of the queries don't work because ONLY_FULL_GROUP_BY is on; if possible, I'd like to keep it that way.

What version of MySQL are you able to use? Can you use MySQL 8.0, or are you required to support MySQL 5.x? — Bill Karwin, Apr 17 '23 at 21:20
Does this answer your question? [Fetch the rows which have the Max value for a column for each distinct value of another column](https://stackoverflow.com/questions/121387/fetch-the-rows-which-have-the-max-value-for-a-column-for-each-distinct-value-of) — philipxy, Apr 17 '23 at 22:24
[Error related to only_full_group_by when executing a query in MySql](https://stackoverflow.com/q/34115174/3404097) — philipxy, Apr 17 '23 at 22:29
Please ask 1 specific researched non-duplicate question. Please either ask re 1 bad query/function with obligatory [mre], including why you think it should return something else or are unsure at the 1st subexpression that it doesn't give what you expect or are stuck, justified by reference to authoritative documentation, or ask about your overall goal giving working parts you can do with justification & a [mre]. Then misunderstood code doesn't belong. But please ask about unexpected behaviour 1st because misconceptions get in the way of your goal. [ask] [Help] Basic questions are faqs. — philipxy, Apr 17 '23 at 22:30
I remedied a lot of disorganization, redundancy & noise. Please consider these aspects of the edited post. As to details of phrasing I edit so much bad writing that i remove whatever I can that is not necessary although I occasionally leave turns of phrase. But I disagree that any "particular phrasing" is worth restoring here. Please don't hide code in snippets, people need to read it. PS Please either ask a MySQL question or a PostgreSQL question & edit per my comments. (Meanwhile) Both duplicates are relevant, you have not given a [mre], you have just dumped wrong code. Pin down to 1 issue. — philipxy, Apr 18 '23 at 11:26
Please do not edit in a way that invalidates reasonable posted answers. Although I don't consider answers to an unclear question that is multiple questions that the answerer knows are duplicates many many times over are reasonable. They are clutter. PS [meta] [meta.se] PS When people think they have to **yell** what they almost always need is good organization & phrasing instead. PS Please avoid social & meta commentary in posts. — philipxy, Apr 18 '23 at 11:45
@philipxy I appreciate your valuable input, I'll add it to my 8.5-year experience of asking and editing questions. Re "yell", this writing technique is called "emphasize", and it is sometimes needed unless it is explicitly banned. This is one of those times. — Parzh from Ukraine, Apr 18 '23 at 14:30
I know it's emphasis & "they almost always need" "good organization & phrasing instead" & that is the case here & that's why I mentioned it. Also now there is no question in this post, you changed a question to a (meta) statement. Also, it is not useful to say you tried things or researched without saying specific results relevant to what you are asking; I left some here in my last edit to show this post had more associated bad code attempts that should each get 1 question. Please act on the rest of my comments. [mre] Also those 2 links I gave are duplicates. Good luck. — philipxy, Apr 18 '23 at 23:19

score 0 · Accepted Answer · edited Apr 17 '23 at 21:47

Here's a solution that uses Window Functions, which are supported by PostgreSQL for a long time, and MySQL 8.0, and most other popular SQL databases, both commercial and open source.

SELECT *
FROM (
  SELECT
    a.*,
    b.account_balance,
    ROW_NUMBER() OVER (PARTITION BY a.account_id ORDER BY t.transaction_timestamp DESC) AS rownum
  FROM account AS a
  JOIN balance_event AS b USING (account_id)
  JOIN transaction AS t USING (transaction_id)
) t
WHERE rownum = 1;

With this solution, you don't need GROUP BY at all, so MySQL's ONLY_FULL_GROUP_BY SQL mode doesn't affect it.

In a grouped table, how to select an ID of a row that has the MAX(timestamp) within the group?

1 Answers1