一个再筛选最小住的问题
PartNumberfirst_date_on_BOM
1013617-00-A2013-03-05
1013617-00-B2013-03-05
1013617-00-C2013-04-24
如上述记录，表中只有两栏，partnumber和first_date_on_BOM，但现在要继续筛选出同一类货号中最早的，比如这里1013617-00-A，1013617-00-B要早于1013617-00-C，但同时我也只需输出1013617-00-A，最终期望的输出是，求大神们帮忙～
PartNumberfirst_date_on_BOM
1013617-00-A2013-03-05
[解决办法]
----------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-10-16 08:00:20
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
--Dec 28 2012 20:23:12
--Copyright (c) Microsoft Corporation
--Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------
--> 测试数据：[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([PartNumber] varchar(12),[first_date_on_BOM] datetime)
insert [huang]
select '1013617-00-A','2013-03-05' union all
select '1013617-00-B','2013-03-05' union all
select '1013617-00-C','2013-04-24'
--------------开始查询--------------------------
SELECT *
FROM dbo.huang
WHERE [PartNumber] IN (
select MIN([PartNumber])
from [huang])
----------------结果----------------------------
/*
PartNumber first_date_on_BOM
------------ -----------------------
1013617-00-A 2013-03-05 00:00:00.000
*/
create table dw
(PartNumber varchar(16),first_date_on_BOM varchar(12))
insert into dw
select '1013617-00-A','2013-03-05' union all
select '1013617-00-B','2013-03-05' union all
select '1013617-00-C','2013-04-24'
select top 1 PartNumber,first_date_on_BOM
from dw a
where not exists
(select 1 from dw b
where datediff(d,b.first_date_on_BOM,a.first_date_on_BOM)>0)
/*
PartNumber first_date_on_BOM
---------------- -----------------
1013617-00-A 2013-03-05
(1 row(s) affected)
*/
create table #tab([PartNumber] varchar(12),[first_date_on_BOM] datetime)
insert #tab
select '1013617-00-A','2013-03-05' union all
select '1013617-00-B','2013-03-05' union all
select '1013617-00-C','2013-04-24'
select PartNumber,first_date_on_BOM from (
select ROW_NUMBER()over(partition by first_date_on_BOM order by PartNumber asc)id,* from #tab
where first_date_on_BOM=(select min(first_date_on_BOM) from #tab)
)a where id=1
--------------------------------------------
PartNumber first_date_on_BOM
------------ -----------------------
1013617-00-A 2013-03-05 00:00:00.000
(1 行受影响)
