MySQL Max() doesn't affect other columns

Question

I have a database contains multiple values with same id but different build. What I am trying is to get only the row with highest build.

Lets say I have a data like below;

|  id  |  build  |  name  |  value  |
|------|---------|--------|---------|
|  1   |  100    | Older  |    5    |
|  1   |  101    | Old    |   10    |
|  1   |  102    | Curr   |   15    |

When I run the following query;

SELECT id, MAX(build), name, value
FROM myTable
WHERE id = 1 (or id in (1..n) in real life)
GROUP BY id

I get the following

|  id  |  build  |  name  |  value  |
|------|---------|--------|---------|
|  1   |  102    | Older  |    5    |

instead of;

|  id  |  build  |  name  |  value  |
|------|---------|--------|---------|
|  1   |  102    |  Curr  |   15    |

I am trying to achieve expected result without subquery. Is there any way to achieve this?

Thanks in advance!

@Stu not allowed to modify the library i am using, and I think whoever wrote this db library, wrote in the time of jesus — Pelin, Aug 02 '22 at 09:24

score 0 · Answer 1 · answered Aug 02 '22 at 09:04

0

You must group by name and value too :

SELECT id, MAX(build), name, value
FROM myTable
WHERE id = 1 (or id in (1..n) in real life)
GROUP BY id, name, value

answered Aug 02 '22 at 09:04

Romylussone

773
1
8
19

Tim Biegeleisen · Answer 2 · 2022-08-02T10:16:52.030

0

I think you want a LIMIT query here:

SELECT id, build, name, value
FROM myTable
ORDER BY build DESC
LIMIT 1;

In the event that there could be two or more records tied for the maximum build value, then use a subquery:

SELECT id, build, name, value
FROM myTable
WHERE build = (SELECT MAX(build) FROM myTable);

Edit: To handle your problem finding the max build record for each separate id groups of records, use either ROW_NUMBER or RANK on MySQL 8+. Assuming ROW_NUMBER:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY build DESC) rn
    FROM myTable
)

SELECT id, build, name, value
FROM cte
WHERE rn = 1;

edited Aug 02 '22 at 10:16

answered Aug 02 '22 at 09:08

Tim Biegeleisen

502,043
27
286
360

Each record belongs to unique `build` so I suppose using the one with `LIMIT` option would be faster than the other one? – Pelin Aug 02 '22 at 09:32
If `build` is unique (PK) then first query is best choice. If same `build` value appears multiple times and you want just rows with maximum value of `build` then second query does the job. If you want maximum `build` for given `ID` (or all of them) then this won't do the job. – Aarlaneth Aug 02 '22 at 09:42
@Aarlaneth You are right, first one works for me, but only for single record since it limits 1. I need to get maximum `build` for each `id` in `WHERE IN (x,y,z)`. Second one doesn't work since each ID can have different `build` number – Pelin Aug 02 '22 at 09:48
@Pelin Check the [answer](https://stackoverflow.com/a/73204849/13535097) I provided, it should do the trick. – Aarlaneth Aug 02 '22 at 09:49

Aarlaneth · Answer 3 · 2022-08-02T10:39:27.840

0

This should do it for you:

WITH tempdata as
(
    SELECT ROW_NUMBER() OVER(PARTITION BY id
    ORDER BY build desc) as RowNumber,
    id,
    build,
    name,
    value
    FROM #temptable
)
SELECT id,build,name,value FROM tempdata WHERE RowNumber = 1

edited Aug 02 '22 at 10:39

answered Aug 02 '22 at 09:08

Aarlaneth

530
6
16

1

MySQL is different from SQL Server...there is no `top` in mysql – Ergest Basha Aug 02 '22 at 10:20

score 0 · Answer 4 · answered Aug 02 '22 at 10:02

0

Many databases process the "FROM" line first, the "WHERE" line second, and the "SELECT" last. Because of that, you are getting the first column that fits your "WHERE" line. You can eliminate this by adding the max requirement in the "WHERE" line such as:

SELECT id, MAX(build), name, value
FROM myTable
WHERE id = 1 (or id in (1..n) in real life) 
     AND build = (SELECT MAX(build) from myTable)
GROUP BY id;

answered Aug 02 '22 at 10:02

OguzDuman

1

I tried this before, and unfortunately it doesn't return any result since max `build` value could be different on each row – Pelin Aug 02 '22 at 10:07

MySQL Max() doesn't affect other columns

4 Answers4