如何选择一个月中的前4个日期. [英] how to select top 4 date in a month.

问题描述

尊敬的先生，

听到的是我的数据，我想选择每个月的前4条记录
听说是9月有8条记录，只能选择每个月的前4条记录.

Dear sir,

hear is my data and i want to select every month top 4 record
hear is sep month have 8 record only select top 4 record of every month .

FreightRate DwtDate  ZoneFrom	ZoneTo	Dwt
0.00	22 Nov 2011	19	14	1
89.00	22 Nov 2011	19	14	2
70.00	22 Nov 2011	19	14	5
49.00	22 Nov 2011	19	14	10
0.00	28 Sep 2011	19	14	1
87.00	28 Sep 2011	19	14	2
68.00	28 Sep 2011	19	14	5
48.00	28 Sep 2011	19	14	10
0.00	06 Sep 2011	19	14	1
89.00	06 Sep 2011	19	14	2
69.00	06 Sep 2011	19	14	5
49.00	06 Sep 2011	19	14	10
0.00	20 Jun 2011	19	14	1
93.00	20 Jun 2011	19	14	2
70.00	20 Jun 2011	19	14	5
51.00	20 Jun 2011	19	14	10
90.00	10 May 2011	19	14	2
64.00	10 May 2011	19	14	5
48.00	10 May 2011	19	14	10
91.00	10 Apr 2011	19	14	2

在此先感谢.

thanks in advance .

推荐答案

Dinesh，
这是:

Hi Dinesh,
Here it is :

select * from (
SELECT *, DATEPART(MONTH, dwtdate) month  , rank() over (partition by DATEPART(MONTH, dwtdate) order by freightrate desc) Ranking
FROM Freight
) marshaled where Ranking <= 4
order by month, ranking

要了解有关排名功能的更多信息:
http://msdn.microsoft.com/en-us/library/ms189798.aspx [ ^ ]


请记住，我以为您的所有数据都在同一年.因此，请考虑对其进行更改并使其适应您的需求.
我也为您的桌子命名为Freight，所以请记住也要更改它.

祝您好运

To read more about ranking functions :
http://msdn.microsoft.com/en-us/library/ms189798.aspx[^]


And remember that I supposed that all of your data is at the same year. So consider to change it and adapt it to your needs.
Also I named your table Freight so remember to change it too.

Good Luck

尝试以下代码:

try this code:

select TOP 4 * from MonthTable where dwtDate LIKE %+ 'SEP' +%

希望对您有帮助，


thnks

hope it helps,


thnks

这篇关于如何选择一个月中的前4个日期.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！