SQL Partitioning Used For Views To Drive Access

This week brought in some challenging tasks required from SQL partitioning...creating a view which mimics a GroupBy in Access.

In SQL, you use PARTITION to get a similar result set to a Group By query.  It will look something like this:

SELECT        *
FROM        (SELECT  *, ROW_NUMBER() OVER (PARTITION BY HASH ORDER BY DateSent desC) AS rn
from           (SELECT hash, survey_id, DateSent, DateCompleted
                      from dbo.tbl_survey_detail) as derivedtable) AS t
WHERE        t.rn = 1

As you can see above, a simple table with a few columns can be partitioned using one of the fields.  It is somewhat confusing to build one the first time, but the end result is quite quick and powerful.

Happy SQL coding!