SQL Partitioning Used For Views To Drive Access

Comments

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!

Related Posts

Access 2016 Relationships

Short blog today, this is a great read on relationships in Access 2016: Access 2016 Relationships Thanks to Microsoft for this one!

Microsoft Access Union Query from Hell. Skill Level: Demi-God

The worst query I ever had to write which powers a master report for the database user.  Here it is.  I don't think I can make it smaller, there were too many weird rules, aggregates, and translations in it.  And we needed to pull different columns based on criteria. ...

0 Comments

0 Comments