求一个统计最新日期数量的 sql

[terlk118]

数据库中的数据格式如下
SDate SName num
2018-01-01 A 10
2018-01-02 A 40
2018-01-03 A 20
2018-01-02 B 10
2018-01-02 C 30
2018-01-04 C 10
2018-01-02 D 20

需要的结果，查找每个SName最新日期的记录
SDate SName num
2018-01-03 A 20
2018-01-02 B 10
2018-01-04 C 10
2018-01-02 D 20

[二月十六]

--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([SDate] Date,[SName] nvarchar(21),[num] int)
Insert #T
select '2018-01-01',N'A',10 union all
select '2018-01-02',N'A',40 union all
select '2018-01-03',N'A',20 union all
select '2018-01-02',N'B',10 union all
select '2018-01-02',N'C',30 union all
select '2018-01-04',N'C',10 union all
select '2018-01-02',N'D',20
Go
--测试数据结束
SELECT  #T.*
FROM    #T
        JOIN ( SELECT   MAX(SDate) AS SDate ,
                        SName
               FROM     #T
               GROUP BY SName
             ) t ON t.SName = #T.SName
                    AND t.SDate = #T.SDate
ORDER BY #T.SName

[二月十六]

--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([SDate] Date,[SName] nvarchar(21),[num] int)
Insert #T
select '2018-01-01',N'A',10 union all
select '2018-01-02',N'A',40 union all
select '2018-01-03',N'A',20 union all
select '2018-01-02',N'B',10 union all
select '2018-01-02',N'C',30 union all
select '2018-01-04',N'C',10 union all
select '2018-01-02',N'D',20
Go
--测试数据结束
SELECT  *
FROM    ( SELECT    * ,
                    ROW_NUMBER() OVER ( PARTITION BY SName ORDER BY SDate DESC ) AS rn
          FROM      #T
        ) t
WHERE   rn = 1

[吉普赛的歌]

--借#1数据
--测试数据
if not object_id(N'Tempdb..#T') is null
    drop table #T
Go
Create table #T([SDate] Date,[SName] nvarchar(21),[num] int)
Insert #T
select '2018-01-01',N'A',10 union all
select '2018-01-02',N'A',40 union all
select '2018-01-03',N'A',20 union all
select '2018-01-02',N'B',10 union all
select '2018-01-02',N'C',30 union all
select '2018-01-04',N'C',10 union all
select '2018-01-02',N'D',20
Go
--测试数据结束
;WITH cte AS (
	SELECT 
	MAX(SDate) AS SDate
	,SName
	FROM #t GROUP BY SName
)
SELECT 
*
,(SELECT TOP 1 b.num FROM #t AS b 
  WHERE a.SDate=b.SDate AND a.SName=b.SName 
  ORDER BY b.num DESC) AS num 
FROM cte AS a;