SQL SERVER AGGREGTE OVER CLAUSE

Just discovered a new way of doing max and min on different date like brioquery (sum by) syntax.

select distinct case_id, max(close_date) over (partition by case_id) as maxdate, min(open_date) over (partition by case_id) as mindate from wfm_subcase

Previously I had to use a derived table to get each of the two dates. Now it can be done in a single clean sql statement.

There is a catch if you uses sql server 2005. You will get error message if you use query designer to edit the query. Since the query designer mode is always the default mode when you create a view, just create a normal query without the over clause then save the view. Next, open the view in edit mode (not design mode) and change the query to a over clause query.