SQL select rows with the highest value on a column.

Question

I have a simplified version of a table for documents here:

Which query will select one row per id and only the greatest rev?

Using the data above, the result from a MySQL query should include two rows: [1, 3, ...] and [2, 1, ..].

I am using checks in the loop to identify and replace outdated revisions from the resultset. Got this problem from here.

Is there an alternative way to reach the same outcome using SQL?

Answer 1

simple where:

select *
from mytable m1
where rev = (select max(rev) 
              from mytable m2
              where m1.id = m2.id) 
                 

Answer 2

Hi Zoe,

Welcome to Q&A Forum; this is a great place to get support, answers, and tips.

Thank you for posting your query; I'll be more than glad to help you out.

To assist you with your issue/problem adequately, it would be constructive for us to reproduce your scenario.

Please provide us with at least the following things:

(1) DDL and sample data population, i.e., CREATE table(s) plus INSERT, T-SQL statements.

(2) What you need to do, i.e., logic and your attempt implementation of it in T-SQL.

(3) Desired output based on the sample data in #1 above.

(4) Your SQL Server version (SELECT @@version;)

But I will try this on my own for you...

I hope my answer is helpful to you,

Your

Bjoern Peters

If the reply was helpful, please upvote and/or accept it as an answer, as this helps others in the community with similar questions. Thanks!

Answer 3

You might use ROW_NUMBER() OVER() to archive your target:

DECLARE @T TABLE (
	id int NOT NULL,
	rev int NOT NULL,
	content varchar(20)
);

INSERT INTO @T VALUES (1, 1, 'c1'), (2, 1, 'c2'), (1, 2, 'c3'), (1, 3, 'c4');

;WITH CTE AS (
	SELECT *, ROW_NUMBER() OVER(PARTITION BY id ORDER BY rev DESC) AS RN
	FROM @T
)

SELECT id, rev, content FROM CTE WHERE RN = 1;