Solved

MySQL: Select rows with highest value in a column only

Posted on 2009-03-29
2
1,238 Views
Last Modified: 2013-12-12
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.
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');

Open in new window

0
Comment
Question by:Chakotay505
2 Comments
 
LVL 39

Accepted Solution

by:
Roger Baklund earned 500 total points
ID: 24012595
Try this:
select test.* 
from test 
join (
  select id,max(version) as max_version 
  from test 
  group by id
) m on test.id=m.id and version=max_version;

Open in new window

0
 
LVL 3

Author Comment

by:Chakotay505
ID: 24012662
Thanks, works great.
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
This article discusses four methods for overlaying images in a container on a web page
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to count occurrences of each item in an array.

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question