Solved

MySQL: Select rows with highest value in a column only

Posted on 2009-03-29
2
1,243 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
how can I see the time difference in only seconds? 4 39
Format Date 7 28
Mysql query one to many 11 40
How do I init a PHP page jusing JQuery? 8 30
Creating and Managing Databases with phpMyAdmin in cPanel.
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

734 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