To order by date part, use EXTRACT() on the date field with the ORDER BY clause. The query returns the count of rows containing these same values and orders the results by count descending.
1SELECT EXTRACT(YEAR FROM CloseDate) AS CloseDate_Year, EXTRACT(MONTH FROM CloseDate) AS CloseDate_Month, COUNT(*) AS cnt
2FROM "OpportunityFiscalEMTimezoned"
3GROUP BY EXTRACT(YEAR FROM CloseDate), EXTRACT(MONTH FROM CloseDate)
4ORDER BY cnt DESC
5LIMIT 10;| CloseDate_Year | CloseDate_Month | cnt |
|---|---|---|
| 2015 | 1 | 3 |
| 2015 | 3 | 2 |
| 2015 | 4 | 2 |
| 2015 | 5 | 2 |
| 2014 | 12 | 1 |
| 2015 | 2 | 1 |
| 2015 | 6 | 1 |
| 2015 | 8 | 1 |
| 2015 | 10 | 1 |
| 2015 | 11 | 1 |
j