如何查询某字段最大值的行记录？

[wuqiwyt]

看下面的图可能描绘的清楚些：


表名为qysds，表中的列分别是纳税人识别号，纳税人名称，补充申报类型，销售收入，应纳税额，所属年度。怎么用SQL语句查询不同纳税人补充申报类型为最大值的哪些行记录信息？例如上表中我想得到是下表中红色颜色信息：

[xdashewan]

select a, max(b) from table
group by a

[还在加载中灬]

SELECT
	T1.*
FROM
	qysds T1
	JOIN(SELECT nsrsbh,MAX(xxsr)xxsr FROM qysds GROUP BY nsrsbh)T2
		ON T1.nsrsbh=T2.nsrsbh AND T1.xxsr=T2.xxsr

应该可以

[wuqiwyt]

引用 1 楼 xdashewan 的回复:

select a, max(b) from table
group by a

好像不行，还是显示全部记录

[--小F--]

select * from qysds as a where not exists(select 1 from qysds where nsrsbh=a.nsrsbh and bcsblx>a.bcsblx)

[卖水果的net]

select * from 
(
select *  , row_number() over(partition by nsrsbh  order by xssr desc)  as rn
from qysds 
) x 
where rn = 1 

[wuqiwyt]

谢谢楼上朋友们，测试后反馈。

[wuqiwyt]

记录的查询是该纳税人补充申报类型bcsblx取最大值的那行，和销售收入无关

[还在加载中灬]

SELECT
    T1.*
FROM
    qysds T1
    JOIN(SELECT nsrsbh,MAX(bcsblx)bcsblx FROM qysds GROUP BY nsrsbh)T2
        ON T1.nsrsbh=T2.nsrsbh AND T1.bcsblx=T2.bcsblx

那这样试试

[reenjie]

select  *  from 
(
select  * , row_number() over(partition by nsrsbh  order by bcsblx desc)  as rId
from qysds 
)  as  a
where rId = 1