SQL select only rows with max value on a column

here is another solution hope it will help someone

Select a.id , a.rev, a.content from Table1 a
inner join
(SELECT id, max(rev) rev FROM Table1 GROUP BY id) x on x.id =a.id and x.rev =a.rev

None of these answers have worked for me.

This is what worked for me.



with score as (select max(score_up) from history)
select history.* from score, history where history.score_up = score.max

Here's another solution to retrieving the records only with a field that has the maximum value for that field. This works for SQL400 which is the platform I work on. In this example, the records with the maximum value in field FIELD5 will be retrieved by the following SQL statement.

SELECT A.KEYFIELD1, A.KEYFIELD2, A.FIELD3, A.FIELD4, A.FIELD5
FROM MYFILE A
WHERE RRN(A) IN
(SELECT RRN(B)
FROM MYFILE B
WHERE B.KEYFIELD1 = A.KEYFIELD1 AND B.KEYFIELD2 = A.KEYFIELD2
ORDER BY B.FIELD5 DESC
FETCH FIRST ROW ONLY)

I used the below to solve a problem of my own. I first created a temp table and inserted the max rev value per unique id.


CREATE TABLE #temp1
(
id varchar(20)
, rev int
)
INSERT INTO #temp1
SELECT a.id, MAX(a.rev) as rev
FROM
(
SELECT id, content, SUM(rev) as rev
FROM YourTable
GROUP BY id, content
) as a
GROUP BY a.id
ORDER BY a.id

I then joined these max values (#temp1) to all of the possible id/content combinations. By doing this, I naturally filter out the non-maximum id/content combinations, and am left with the only max rev values for each.

SELECT a.id, a.rev, content
FROM #temp1 as a
LEFT JOIN
(
SELECT id, content, SUM(rev) as rev
FROM YourTable
GROUP BY id, content
) as b on a.id = b.id and a.rev = b.rev
GROUP BY a.id, a.rev, b.content
ORDER BY a.id

**NOT mySQL**, but for other people finding this question and using SQL, another way to resolve the [tag:greatest-n-per-group] problem is using [`Cross Apply`][cross apply] in MS SQL


WITH DocIds AS (SELECT DISTINCT id FROM docs)

SELECT d2.id, d2.rev, d2.content
FROM DocIds d1
CROSS APPLY (
SELECT Top 1 * FROM docs d
WHERE d.id = d1.id
ORDER BY rev DESC
) d2

###[Here's an example in SqlFiddle][sql fiddle]

[cross apply]:

[To see links please register here]

[sql fiddle]:

[To see links please register here]

You can make the select without a join when you combine the `rev` and `id` into one `maxRevId` value for `MAX()` and then split it back to original values:

SELECT maxRevId & ((1 << 32) - 1) as id, maxRevId >> 32 AS rev
FROM (SELECT MAX(((rev << 32) | id)) AS maxRevId
FROM YourTable
GROUP BY id) x;

This is especially fast when there is a complex join instead of a single table. With the traditional approaches the complex join would be done twice.

The above combination is simple with bit functions when `rev` and `id` are `INT UNSIGNED` (32 bit) and combined value fits to `BIGINT UNSIGNED` (64 bit). When the `id` & `rev` are larger than 32-bit values or made of multiple columns, you need combine the value into e.g. a binary value with suitable padding for `MAX()`.

I think, You want this?

select * from docs where (id, rev) IN (select id, max(rev) as rev from docs group by id order by id)

SQL Fiddle :
[Check here](

[To see links please register here]

)

How about this:

SELECT all_fields.*
FROM (SELECT id, MAX(rev) FROM yourtable GROUP BY id) AS max_recs
LEFT OUTER JOIN yourtable AS all_fields
ON max_recs.id = all_fields.id

# Explanation
This is not pure SQL. This will use the SQLAlchemy ORM.

I came here looking for SQLAlchemy help, so I will duplicate Adrian Carneiro's answer with the python/SQLAlchemy version, specifically the outer join part.

This query answers the question of:

*"Can you return me the records in this group of records (based on same id) that have the highest version number".*

This allows me to duplicate the record, update it, increment its version number, and have the copy of the old version in such a way that I can show change over time.


# Code
```
MyTableAlias = aliased(MyTable)
newest_records = appdb.session.query(MyTable).select_from(join(
MyTable,
MyTableAlias,
onclause=and_(
MyTable.id == MyTableAlias.id,
MyTable.version_int < MyTableAlias.version_int
),
isouter=True
)
).filter(
MyTableAlias.id == None,
).all()
```

Tested on a PostgreSQL database.

SELECT *
FROM Employee
where Employee.Salary in (select max(salary) from Employee group by Employe_id)
ORDER BY Employee.Salary