declare @t table(物料 char(1),日期 datetime,单号 char(9),明细 char(3))
insert into @t select 'A', '2006-2-3', 'PO0602001', '010'
union all select 'A', '2006-3-3', 'PO0603001', '001'
union all select 'B', '2006-2-3', 'PO0602001', '010'
union all select 'B', '2006-3-5', 'PO0603001', '001'
union all select 'B', '2006-3-5', 'PO0603001', '011'
union all select 'B', '2006-2-8', 'PO0602001', '010'select 物料,日期=max(日期),单号=max(单号),明细=max(明细) from @t group by 物料 Order by 物料,日期 DESC,单号 DESC,明细 DESC
---结果
/*(6 行受影响)
物料 日期 单号 明细
---- ----------------------- --------- ----
A 2006-03-03 00:00:00.000 PO0603001 010
B 2006-03-05 00:00:00.000 PO0603001 011(2 行受影响)*/
insert into @t select 'A', '2006-2-3', 'PO0602001', '010'
union all select 'A', '2006-3-3', 'PO0603001', '001'
union all select 'B', '2006-2-3', 'PO0602001', '010'
union all select 'B', '2006-3-5', 'PO0603001', '001'
union all select 'B', '2006-3-5', 'PO0603001', '011'
union all select 'B', '2006-2-8', 'PO0602001', '010'select 物料,日期=max(日期),单号=max(单号),明细=max(明细) from @t group by 物料 Order by 物料,日期 DESC,单号 DESC,明细 DESC
---结果
/*(6 行受影响)
物料 日期 单号 明细
---- ----------------------- --------- ----
A 2006-03-03 00:00:00.000 PO0603001 010
B 2006-03-05 00:00:00.000 PO0603001 011(2 行受影响)*/
insert T select 'A', '2006-2-3', 'PO0602001', '010'
union all select 'A', '2006-3-3', 'PO0603001', '001'union all select 'B', '2006-2-3', 'PO0602001', '010'
union all select 'B', '2006-3-5', 'PO0603001', '001'
union all select 'B', '2006-3-5', 'PO0603001', '011'
union all select 'B', '2006-2-8', 'PO0602001', '010'select ID=identity(int, 1, 1), * into #T from T
order by 物料,日期 DESC,单号 DESC,明细 DESCselect 物料, 日期, 单号, 明细 from #T as tmp
where (select count(*) from #T where 物料=tmp.物料 and ID<=tmp.ID)=1--result
物料 日期 单号 明细
---------- ------------------------------------------------------ ---------- ----------
A 2006-03-03 00:00:00.000 PO0603001 001
B 2006-03-05 00:00:00.000 PO0603001 011(2 row(s) affected)