The following is a little SQL challenge I have so far been unable
to complte myself. Lets say I have a table with transactions for
different products.
CREATE TABLE transactions (
product_id int(11) NOT NULL,
transaction_id int(11) NOT NULL,
price decimal(18,2) NOT NULL
);
The transaction_id's increase with time. In reality there is a
transaction_date column, but I wanted to keep the DDL as portable
as possible for this example. Here is some sample data.
INSERT INTO transactions VALUES (1, 1, '2.00');
INSERT INTO transactions VALUES (1, 2, '3.00');
INSERT INTO transactions VALUES (1, 4, '3.00');
INSERT INTO transactions VALUES (1, 5, '1.00');
INSERT INTO transactions VALUES (2, 6, '3.00');
INSERT INTO transactions VALUES (2, 7, '5.00');
INSERT INTO transactions VALUES (2, 8, '3.00');
INSERT INTO transactions VALUES (2, 9, '3.00');
Now a query to get the last price for each product could look
like …
[Read more]