Try it out: Play a game online and do some good while you’re at it.
Pages
Categories
Old Posts
- December 2009 (4)
- November 2009 (1)
- October 2009 (1)
Act on Global Warming
Get active about global warming and the failure of the Copenhagen conference, click the logo and send an email to our ‘leaders’.
Calculate the Running Balance
Another fine use of my sequencing method is to calculate the running balance on a ledger:
SELECT t1.CompanyID, t1.CompanyName, t1.PostingDate, t1.Total, Sum(t2.Total) AS balance
FROM tmpDirLoanData AS t1 INNER JOIN tmpDirLoanData AS t2 ON t1.CompanyID = t2.CompanyID
GROUP BY t1.CompanyID, t1.CompanyName, t1.PostingDate, t1.Total, [t1].[postingdate]>=[t2].[postingdate]
HAVING ([t1].[postingdate]>=[t2].[postingdate])=True;
Here I am using the same method to calculate a Sum instead of a Count – that is the only difference.
PS this is JET sql in case anyone is wondering.
Find user’s most recent post in each thread
One common problem that my sequence method can help to solve is how to display the most recent entry that a user has made in each thread that the user has contributed to:
SELECT * FROM posts WHERE usedid = $userid AND postid IN
(SELECT t1.postid, COUNT(t1.postid) AS sequence, t1.postid <= t2.postid AS flg
FROM posts t1 INNER JOIN posts t2 ON t1.userid = t2.userid AND t1.threadid = t2.threadid
GROUP BY t1.userid, t1.threadid, flg
HAVING flg = TRUE AND sequence = 1)
Notice that I use the reverse sequence here (t1.postid <= t2.postid) so that the posts are numbered in descending order with the most recent = 1.
How to Sequence each Sub-set of Records
I first developed this method in answer to a very specific problem posted on PHP Builder:keep last 5 logs and delete rest. It is an ideal solution to this knotty problem where there are an unknown number of records in each subset.
The problem is compounded because records will have been inserted in a more or less random order across the sets. To solve it we have to find the sequence within each sub-set.
Now some DBs such as MS SQL and Oracle have that functionality built in, but others do not. For these we need another approach, and the method is to use a self-join with grouping to generate the sequence number.
SELECT t1.userid, COUNT(t1.tableid) AS sequence, t1.tableid, t1.tableid >= t2.tableid AS flg
FROM table t1 INNER JOIN table t2 ON t1.userid = t2.userid
GROUP BY t1.userid, t1.tableid, flg
HAVING flg = TRUE
What this does is to join each row to itself and all those within the same sub-set, in this case ‘userid’, that were inserted before it and then count them. So the first entry for each user is only joined to itself = 1, the second is joined to itself and the first = 2, third to itself and the first two = 3, etc.
To reverse the sequence simply reverse the selection
t1.tableid <= t2.tableid AS flg
So to solve the original problem we just need the reverse sequence which we can then use to delete all records with a sequence greater than 5, neat huh.
DELETE FROM table INNER JOIN
(SELECT t1.userid, COUNT(t1.tableid) AS sequence, t1.tableid, t1.tableid <= t2.tableid AS flg
FROM table t1 INNER JOIN table t2 USING(userid)
GROUP BY t1.userid, t1.tableid, flg
HAVING flg = TRUE) AS q1 USING(tableid)
WHERE sequence > 5
PS this is of course MySQL syntax and many other RDBMS do not support the USING operator for joins
Some cool Flash widgets
Just came across some cool Flash widgets on abowman.com, check them out, click on this one to feed the fish.
I had another one in this post but the two together kind of slow each other down. Shame because the Penguins were pretty funny. Perhaps I’ll rotate them occasionally.