# by取最大值 - mysql group最大値

## 為每組分組的SQL結果獲取最大值的記錄 (12)

axiac的解決方案是最適合我的。 然而，我有一個額外的複雜性：計算“最大值”，來自兩列。

``````SELECT o1.* WHERE
(SELECT o.*
FROM `Persons` o
LEFT JOIN `Persons` b
ON o.Group = b.Group AND o.Age < b.Age
WHERE b.Age is NULL) o1
LEFT JOIN
(SELECT o.*
FROM `Persons` o
LEFT JOIN `Persons` b
ON o.Group = b.Group AND o.Age < b.Age
WHERE b.Age is NULL) o2
ON o1.Group = o2.Group AND o1.Height < o2.Height
WHERE o2.Height is NULL;
``````

``````Person | Group | Age
---
Bob  | 1     | 32
Jill | 1     | 34
Shawn| 1     | 42
Jake | 2     | 29
Paul | 2     | 36
Laura| 2     | 39
``````

``````Shawn | 1     | 42
Laura | 2     | 39
``````

``````CREATE TABLE p
(
person NVARCHAR(10),
gp INT,
age INT
);
GO
INSERT  INTO p
VALUES  ('Bob', 1, 32);
INSERT  INTO p
VALUES  ('Jill', 1, 34);
INSERT  INTO p
VALUES  ('Shawn', 1, 42);
INSERT  INTO p
VALUES  ('Jake', 2, 29);
INSERT  INTO p
VALUES  ('Paul', 2, 36);
INSERT  INTO p
VALUES  ('Laura', 2, 39);
GO

SELECT  t.person, t.gp, t.age
FROM    (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY gp ORDER BY age DESC) row
FROM   p
) t
WHERE   t.row = 1;
``````

``````SELECT
yourtable.*
FROM
yourtable
JOIN (
SELECT `Group`, MAX(Age) AS age
FROM yourtable
GROUP BY `Group`
) maxage
/* join subquery against both Group and Age values */
ON yourtable.`Group` = maxage.`Group`
AND yourtable.Age = maxage.age
``````

``````WITH MyCTE(MaxPKID, SomeColumn1)
AS(
SELECT MAX(a.MyTablePKID) AS MaxPKID, a.SomeColumn1
FROM MyTable1 a
GROUP BY a.SomeColumn1
)
FROM MyTable1 b
INNER JOIN MyCTE c ON c.MaxPKID = b.MyTablePKID
GROUP BY b.MyTablePKID, b.SomeColumn1, b.SomeColumn2

--Note: MyTablePKID is the PrimaryKey of MyTable
``````

``````SELECT a.Person, a.Group, a.Age FROM [TABLE_NAME] a
INNER JOIN
(
SELECT `Group`, MAX(Age) AS oldest FROM [TABLE_NAME]
GROUP BY `Group`
) b ON a.Group = b.Group AND a.Age = b.oldest
``````

``````select *
from (select * from mytable order by `Group`, age desc, Person) x
group by `Group`
``````

### 版本5.7更新：

``````SELECT a.* FROM `mytable` AS a
WHERE a.age IN( SELECT MAX(b.age) AS age FROM `mytable` AS b GROUP BY b.group )
ORDER BY a.group ASC, a.person ASC
``````

``````SELECT SUBSTRING_INDEX(GROUP_CONCAT(column_x ORDER BY column_y),',',1) AS xyz,
column_z
FROM table_name
GROUP BY column_z;
``````

``````SELECT o.*
FROM `Persons` o                    # 'o' from 'oldest person in group'
LEFT JOIN `Persons` b             # 'b' from 'bigger age'
ON o.Group = b.Group AND o.Age < b.Age
``````

### 怎麼運行的：

`LEFT JOIN`使它與來自`b` （'沒有最大年齡的組'）中的滿足`NULL`的行匹配組中最老的人（包括在他們組中單獨的人）。

`WHERE`子句只保留從`b`提取的字段中具有`NULL` s的行。 他們是每個組別中年齡最大的人。

### 更多的讀物

SQL解決方案：避免數據庫編程的陷阱 “一書中解釋了這個解決方案和許多其他解決方案

``````select O.*              -- > O for oldest table
from people O , people T
where O.grp = T.grp and
O.Age =
(select max(T.age) from people T where O.grp = T.grp
group by T.grp)
group by O.grp;
``````

``````SELECT person, group,
GROUP_CONCAT(
DISTINCT age
ORDER BY age DESC SEPARATOR ', follow up: '
)
FROM sql_table
GROUP BY group;
``````

``````with CTE as
(select Person,
[Group], Age, RN= Row_Number()
over(partition by [Group]
order by Age desc)
from yourtable)`

`select Person, Age from CTE where RN = 1`
``````