SQL查询显示每个类别中的最大值 [英] SQL Query to Display Max value in each category

问题描述

我有一张如下表格

Hi,

I have a table like below

ID	RefNo	Prefix	Seq
179	55	I	1
180	55	A	1
182	55	A	2
183	55	A	3
184	56	A	1
181	56	I	1
187	56	A	2
188	56	A	3
189	59	I	1
190	59	A	1

我需要从此表中获得支出on RefNo，Prefix，seq

And i need outpot from this table Based on RefNo , Prefix , seq

ID	RefNo	Prefix	Seq
183	55	A	3
188	56	A	3
190	59	A	1

基于RefNo字段中前缀的Seq号的MAx应作为输出



PLease帮我写SQL查询



谢谢

Mohan

MAx of Seq number based on prefix in RefNo field should be as output

PLease help me to write SQL Query

Thanks
Mohan

推荐答案

使用GROUP的组合BY和MAX应该给你正确的结果。

Using a combination of GROUP BY and MAX should give you the right results.

SELECT * FROM
(
SELECT row_number() OVER (PARTITION BY RefNo order by RefNo,Prefix ,Seq DESC) srno, ID, RefNo, Prefix, Seq
from TableName
) as temp
where srno = 1

快乐编码！

：）

Happy Coding!
:)

正如Abhinav S.所提到的，可以使用 GROUP BY 子句。只有一个注释： ID 必须省略。

As Abhinav S. had mentioned, it's possible to use GROUP BY clause. There is only one note: ID must be omitted.

SELECT RefNo, Prefix, MAX(Seq) AS Seq
FROM TableName
GROUP BY RefNo, Prefix

这篇关于SQL查询显示每个类别中的最大值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！