Derived Table

`Derived table` in mssql is equivalent to `inline view` in Oracle or `subselect` in Postgres. Its purpose is to let us use a `select` query as a source with an alias. Example

Select * from (select case_id,open_date from tbl_data) AS S

It looked stupid to have such a query which does not help in anything. However, its very much more useful than just that.

For example. You wish to get the last close date from a subcase while retrieving a main case data. This could only be achieved if you create a view to get the last close date or create a temporary table to get the last close date. Once you can get the data from the view or temp table, you join it to main case to get the final result.

Creating temp table means you have to do a number of steps to achieve it and have to remember to remove the temp table. Creating a view is easier as it will not need maintenance after that. However, you have to do one query in two places and you cannot create the query on the run. Is it at all possible to use just one query to get the desired results?

`Derived table` is the answer. The following is a working example from wfm (our server) database.

SELECT M.Case_id, S.close_date
FROM dbo.T_CASE AS M LEFT OUTER JOIN
(SELECT case_acc_id, MAX(close_date) AS close_date
FROM dbo.T_SUBCASE
GROUP BY case_acc_id) AS S ON M.Case_id = S.case_acc_id