SQL FIRST aggregate function

As you all knows very well that the FIRST aggregate function does not exists in sql server.

There are actually tweaks to make it work. Heres how.

select case_id, RowNumber from (select case_id, ROW_NUMBER() over (partition by case_id order by subcase_id) as RowNumber from tbl_case) as tblcase where RowNumber=1

The above sql actually returns the first subcase information since i use ORDER BY SUBCASE_ID in PARTITION BY.

This is one of those odd cases where you do a select from a derived table.

This query is particularly useful when you happen to have duplicate subcase_id. It will return unique rows.

Obviously, if you just do like the example above, it is kind of going the long way. A simple DISTINCT and a WHERE clause can do much better job than that. But if you have duplicate subcase_id then your DISTINCT,WHERE method may not work as per expected. Moreover, you could make use of the derived table to do MAX and MIN aggregate function on other fields across all subcases.

PARTITION BY works similar to BRIOQUERY SUM BY method. It does not require a GROUP BY which is somtimes very obstructing to the AGGREGATE function. For example you want to do an aggregate but also want to include the case_title field. That is impossible for normal query with GROUP BY since case_title is almost unique to each case. After getting the aggregated results then you use the above SELECT FROM DERIVED TABLE method to do further filtering to get only the results you want.