Chakotay505
asked on
MySQL: Select rows with highest value in a column only
Hello,
I want to fetch rows with the highest value in a column.
With this table contents given:
ID --- VERSION --- text
1 --- 1 --- abc
1 --- 2 --- def
1 --- 3 --- ghj
2 --- 1 --- mno
3 --- 1 --- uvw
3 --- 2 --- xyz
I want a query that gives me
ID --- VERSION --- text
1 --- 3 --- ghj
2 --- 1 --- mno
3 --- 2 --- xyz
as result. I need a performant query, as this query will by executed quite often (possibly multiple times when visiting a page).
I startet experimenting with GROUP BY, but couldn't quite get to a working result.
I'd be glad if someone could help me on this one.
I want to fetch rows with the highest value in a column.
With this table contents given:
ID --- VERSION --- text
1 --- 1 --- abc
1 --- 2 --- def
1 --- 3 --- ghj
2 --- 1 --- mno
3 --- 1 --- uvw
3 --- 2 --- xyz
I want a query that gives me
ID --- VERSION --- text
1 --- 3 --- ghj
2 --- 1 --- mno
3 --- 2 --- xyz
as result. I need a performant query, as this query will by executed quite often (possibly multiple times when visiting a page).
I startet experimenting with GROUP BY, but couldn't quite get to a working result.
I'd be glad if someone could help me on this one.
CREATE TABLE test(id INT UNSIGNED NOT NULL AUTO_INCREMENT, version SMALLINT NOT NULL DEFAULT 1, text TEXT, PRIMARY KEY(id, version));
INSERT INTO test(id, version, text) values(1, 1, 'abc'), (1, 2, 'def'), (1, 3, 'ghj'), (2, 1, 'mno'), (3, 1, 'uvw'), (3, 2, 'xyz');
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER