SQL查询:返回组的最大值记录 [英] SQL Query: Return Max value record of a Group

问题描述

我有一个结构相似的示例表&数据如下所示:

I have a sample table with similar structure & data as shown below:

+------+---------+-------------+------------+
| S_ID | S_NAME  | SUBJECT     | MARK_VALUE |
+------+---------+-------------+------------+
|    1 | Stud    | SUB_1       |         50 |
|    2 | Stud    | SUB_2       |         60 |
|    3 | Stud    | SUB_3       |         70 |
|    4 |  Stud_1 | SUB_1       |         40 |
|    5 |  Stud_1 | SUB_2       |         50 |
|    6 |  Stud_2 | SUB_2       |         40 |
+------+---------+-------------+------------+

表在每个学生出现的所有主题中都对每个学生进行了合并标记.

Table has consolidated mark of each student in all subjects each that student has appeared.

请帮助我，编写一个查询以提取每个学生(不分学科/其他学生)获得的最高分，如下所示:

Please help me, to write a query to extract MAXIMUM mark obtained by each student (irrespective of subject/other students), as below:

按S_Name和amp;最大值(MARK_Value)

Group by S_Name & Max(MARK_Value)

+------+---------+-------------+------------+
| S_ID | S_NAME  | SUBJECT     | MAX_MARK   |
+------+---------+-------------+------------+
|    3 | Stud    | SUB_3       |         70 |
|    5 |  Stud_1 | SUB_2       |         50 |
|    6 |  Stud_2 | SUB_2       |         40 |
+------+---------+-------------+------------+

推荐答案

使用row_number()窗口函数

select * from
 ( select *,
  row_number()over(partition by s_name order by MARK_VALUE desc) rn
 from table_name
) t where t.rn=1

或者您可以使用相关的子查询

or you can use corelated subquery

select t1.* from table_name t1
  where t.MARK_VALUE=(select max(MARK_VALUE) from table_name t2 where t2.S_NAME=t1.S_NAME)

这篇关于SQL查询:返回组的最大值记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！