select (select top 1 a.po from tab a where a.lot=c.lot order by a.andate) as po,
lot,
(select min(andate) from tab b where b.lot=c.lot) as andate
from tab c
group by LOT
lot,
(select min(andate) from tab b where b.lot=c.lot) as andate
from tab c
group by LOT
INTO #tempTable
FROM theTable
ORDER BY LOT, AnDateSELECT PO, LOT, AnDate
FROM #tempTable
INNER JOIN (
SELECT LOT, MAX(AutoID) AS MaxID
FROM #tempTable
GROUP BY LOT
) B ON B.LOT = A.LOT
AND B.MaxID = A.AutoID
select distinct * from a where andate in (select min(andate) from a )
insert into @a
select '235253', '33391', '2004-5-2 0:00' union all
select '235254', '33391', '2004-5-9 0:00' union all
select '235255', '33391', '2004-5-16 0:00' union all
select '235251', '33391', '2004-3-28 0:00' union all
select '235251', '33391', '2004-4-18 0:00' union all
select '235252', '33391', '2004-4-11 0:00' union all
select '235249', '33391', '2004-3-28 0:00' union all
select '235250', '33391', '2004-3-28 0:00' union all
select '235250', '33391', '2004-4-18 0:00' union all
select '235260', '33392', '2004-5-9 0:00' union all
select '235258', '33392', '2004-3-28 0:00' union all
select '235258', '33392', '2004-4-25 0:00' union all
select '235259', '33392', '2004-4-11 0:00' union all
select '235256', '33392', '2004-3-28 0:00' union all
select '235257', '33392', '2004-3-28 0:00' union all
select '235257', '33392', '2004-4-25 0:00'--查询一
select PO=min(PO),a.LOT,a.AnDate from
(
select LOT,AnDate=Min(AnDate) from @a
group by LOT
) x,@a a
where x.LOT=a.LOT and x.AnDate=a.AnDate
group by a.LOT,a.AnDate--查询二(如果PO,AnDate不同时为小就不正确)
select PO=Min(PO),Lot,AnDate=Min(AnDate)
from @a
group by LOT