Just came across some cool Flash widgets on abowman.com, check them out.
Pengiuns: they follow your mouse around.
Fish: click on the pond to feed the fish
[edit]turns out it was the FollowSite plugin that was slowing up my blog – lousy plugin that does not work[/edit]
Try it out: Play a game online and do some good while you’re at it.
Get active about global warming and the failure of the Copenhagen conference, click the logo and send an email to our ‘leaders’.
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.
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.
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
The Most Truth That I Know
If we could conquer greed then we would conquer need.
This is the most truth that I know.
I only wish it could be so.