SQL aggregate function

[Tim Ferguson]

Dear All

This is not strictly a tables/ db design question, but I have friends and
experts I trust here!

I have a table of subscriptions paid to a society:

Subs(*MemberNum, *YearNum, CategoryType, PaidAmount, PaidDate, etc)

Now, if I want to get the most recent year that a member paid, it's
simple:

SELECT MemberNum, MAX(YearNum) AS CurrentYear
FROM Subs
GROUP BY MemberNum

but if I want to retrieve (say) the membership type in that year it's not
so easy:

SELECT MemberNum, Max(YearNum), First(CategoryType)
FROM Subs
GROUP BY MemberNum

does not retrieve values from the same record. I know I can do it with a
subselect and a join

SELECT o.MemberNum, o.YearNum, o.CategoryType
FROM Subs AS o
RIGHT JOIN
( SELECT MemberNum, MAX(YearNum) AS CurrentYear
FROM Subs
GROUP BY MemberNum
) AS i
ON i.MemberNum = o.MemberNum
AND i.CurrentYear = o.YearNum


but it seems that there should be a simpler way. What am I missing?

All the best


Tim F

 

[Joan Wild]

This describes three methods, including the one you don't like (why not,
BTW).
http://www.mvps.org/access/queries/qry0020.htm

 

[Jamie Collins]

I know I can do it with a
subselect and a join
but it seems that there should be a simpler way. What am I missing?

With a 'history' table (more correctly a 'valid-time' state table) it
is very common to have a 'current' VIEW (stored Query) expressly to
avoid having to write a subquery each time.

Jamie.

--

 

[Tim Ferguson]

This describes three methods, including the one you don't like (why not,
BTW).
http://www.mvps.org/access/queries/qry0020.htm

Thanks for that: it's comforting to know that I hadn't missed something
simpler. It's not that I don't like the subselect, I just felt that for
such a common operation there was likely to be a simpler way to do it.

Thanks for the input.

Tim

 

[Tim Ferguson]

With a 'history' table (more correctly a 'valid-time' state table) it
is very common to have a 'current' VIEW (stored Query) expressly to
avoid having to write a subquery each time.

Point taken. Thanks Jamie.


Tim F

 

[Jamie Collins]

Point taken. Thanks

I've read recently about 'temporal partitioning' where a valid-time
table is broken into a 'current' table and a 'historical' table. The
conclusion was some queries/modifications were simplified, others made
more complex (Snodgrass, 'Developing Time-Oriented Database
Applications in SQL', P216). Could be worth you considering:

http://www.cs.arizona.edu/~rts/tdbbook.pdf

Jamie.

--