Order by in SQL Server derived table

building semantic layer
1

I know SQL server does not allow ORDER BY in subqueries, just trying to think if there is another way around this where I can get my data ordered in the derived table in a desc manner.

Here is my derived table query

select count(flag),key1,createdate
from
mytable
where createdate> ‘2012-01-01’
group by key1,createdate
order by createdate desc

Some folks mentioned to use rank over partition by but not sure how this will re-arrange or order by data

monty4u :us: (BOB member since 2006-12-12)

2

Try a common table expression as opposed to a subquery?

ABILtd :uk: (BOB member since 2006-02-08)

3

Or maybe use RANK OVER functionality.