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.

Update me when site is updated
Posted in Uncategorized | Leave a comment

Play a game and donate water

Try it out: Play a game online and do some good while you’re at it.

Update me when site is updated
Posted in Uncategorized | Leave a comment

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’.

Act Now - Change the Future


;
Actions you can take now


Update me when site is updated
Posted in Uncategorized | Tagged | Leave a comment

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.

Update me when site is updated
Posted in SQL Tips n Tricks | Tagged , , , | Leave a comment

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.

Update me when site is updated
Posted in SQL Tips n Tricks | Tagged , , , | 1 Comment

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

Update me when site is updated
Posted in SQL Tips n Tricks | Tagged , , , | 1 Comment