Friday, July 25, 2008

Apress books cheap in Sydney

A heads up for any techies in Sydney,
Basement Books (near Central Station) has a whole bunch of Apress books on sale.

There were half a dozen copies of Jonathan Lewis' Cost Based Oracle for $20, plus Beginning PL/SQL and Oracle PHP (though no sign of Tom's book). It isn't just Oracle. The stock includes SQL Server, MySQL, general database, Ruby, Joomla....

I picked up a couple of Javascript books myself, at about a quarter of the list price.

Worth a trip in, but I've got no idea how long they'll stay in stock.

Small slices of pie

Small post to get back into things.

I had to do a Pie Chart in Apex. The problem was, on the basic select, some of the labels ran together.

If you order the items by how big the slice of pie is, then all the items with small values will be next to each other so their labels will be close and there's a good chance of them overlapping. What I wanted to do was re-order the items to minimize the chance of small items being placed together. Obviously if there are lots of small items and only a couple of big ones that won't always be possible, but its worth a try.

What I came up with was analytics.

Firstly, I rank the items by the value, both ascending and descending. The item with the highest value has both the highest and lowest rankings, and so does the item with the lowest. Ones in the middle stay in the middle. I order by the greatest of the two rankings, with a slight adjustment to the 'descending' ranking to avoid ordering collisions.

select link, label, value
(select null link, label, count(*) value,
rank() over (order by count(*)) rnk,
rank() over (order by count(*) desc) rnk2
(select case when rownum /2 = trunc(rownum/2) then 'able'
when rownum /3 = trunc(rownum/3) then 'baker'
when rownum /5 = trunc(rownum/5) then 'charlie'
when rownum /7 = trunc(rownum/7) then 'delta'
when rownum /11 = trunc(rownum/11) then 'echo'
when rownum /13 = trunc(rownum/13) then 'foxtrot'
when rownum /17 = trunc(rownum/17) then 'golf'
else 'juliet' end label
from dual
connect by level < 1000) a
group by label)
order by greatest(rnk,rnk2-.1) desc

This way, the biggest slice gets put next to the smallest and so on, until the two middling ones are next to each other. And so there's a much reduced chance of label overlaps.