sql选取每个分组中的第一条数据

1
2
3
4
5
6

CREATE TABLE #Order1
(
OrderName VARCHAR(50),
RequestDate datetime,
OrderCount INT
)

Create Table #Order1 ( OrderName varchar(50), RequestDate datetime, OrderCount int )

1
2
3
4
5
6
7

INSERT INTO #Order1 VALUES('衣服','2013-06-02',2)
INSERT INTO #Order1 VALUES('衣服','2013-06-03',4)
INSERT INTO #Order1 VALUES('衣服','2013-06-04',5)
 
INSERT INTO #Order1 VALUES('鞋','2013-06-02',3)
INSERT INTO #Order1 VALUES('鞋','2013-06-03',7)
INSERT INTO #Order1 VALUES('鞋','2013-06-04',8)

insert into #Order1 values('衣服','2013-06-02',2) insert into #Order1 values('衣服','2013-06-03',4) insert into #Order1 values('衣服','2013-06-04',5) insert into #Order1 values('鞋','2013-06-02',3) insert into #Order1 values('鞋','2013-06-03',7) insert into #Order1 values('鞋','2013-06-04',8)

1
2
3
4
5
6

SELECT * FROM
(
SELECT ROW_NUMBER()OVER(PARTITION BY OrderName ORDER BY RequestDate DESC) AS rownum, OrderCount,OrderName 
FROM #Order1
) T
WHERE T.rownum = 1

select * from ( select ROW_NUMBER()over(PARTITION By OrderName ORDER BY RequestDate DESC) as rownum, OrderCount,OrderName from #Order1 ) T where T.rownum = 1