alanjames.org

Recently when optimising an SQL query I needed to return a specific subset of records from a large table. The brilliant Common Queries Tree pointed me in the right direction, but the examples didn’t quite cover my specific case.

I needed the records with the maximal value of x and the minimal value of y for that x.

For example, suppose you had a table of second hand books and each book has a quality and price. There are many copies of each book but you’re only interested in the best examples of each book and at the lowest price for that quality.


CREATE TABLE Books( id int,
ISBN varchar(10),
Quality int,
price decimal(6,2)
);
INSERT INTO Books VALUES
( 1, '0618551050', 4, 5.00 ),
( 2, '0618551050', 4, 6.00 ),
( 3, '0618551050', 2, 3.00 ),
( 4, '0786884061', 1, 8.00 ),
( 5, '0786884061', 3, 8.00 );


Here we are only interested in records 1 and 5. The Common Queries Tree describes this as a within-group aggregates problem, and as it notes the correlated subquery is the most obvious solution but it was slow.

I started off with something like this
Select * from Books as b1
where b1.id = (SELECT id from Books where Books.ISBN = b1.ISBN order by Quality desc, Price limit 1);

But with almost a million records in the table, a select on each one was taking forever. After a bit of head scratching I managed to use a left self exclusion join to find the records I wanted.

select b1.* from Books as b1
left join Books as b2 on b1.ISBN = b2.ISBN and b1.Quality < b2.Quality
left join Books as b3 on b1.ISBN = b3.ISBN and b1.Quality = b3.Quality and b1.Price > b3.Price
WHERE b2.id is NULL and b3.id is NULL;

Here the idea is to select as b1 the records from Books where its impossible to find a book, b2, that has a higher quality and impossible to find a book, b3 with the same quality as b1 but a lower price. If there was another book with a higher quality then b2.id would not be NULL so those records are excluded. Similarly if a book at the same quality had been found but with a lower price, b3.id would not be null.

This was much faster on my data set but it does produce a slightly different result when two items are indistinguishable.
insert into Books VALUES ( 6, '0786884061', 3, 8.00 );

With that my initial query would return either record 5 or 6 and the new query returns both. So group by b1.ISBN to restore the old behaviour. Once you’re happy, save your query into a view so you don’t have to look at it again.

Got a better solution ? If you can optimise my view you’ll make me very happy.

Alan.

Yesterday I plugged my Nokia 6500 Classic phone into my Jolicloud powered netbook, told the network manager I was on a Vodafone contract and bang, instant 3G internet access. It could hardly be simpler.

Once its set up, you can edit the configuration and see where it’s put the APN and other connection info, and edit to match your provider if its not on the list of presets.

Bluetooth is trickier, the GUI tool had a go at it but failed miserably just like it had on plain Ubuntu. Pairing and trasnferring files it’ll do but not dial up networking. For that you’ll have to use the guide I found here, which worked perfectly on Ubuntu and so should also work for Jolicloud.

I’m really happy with the cabled operation. My Netbook, a first generation EEE PC 701 (with a RAM upgrade) doesn’t have bluetooth built in so instead of carrying around a bluetooth dongle I can just carry the phones USB cable instead. I’ll be using less power too without the unnecessary radio chatter.

I might actually have to do some tech support from Glastonbury this year so I’m glad this is working so well now.

beerandnoodles posted a photo:

Up late at the computer again.

I use a Twitter client named Spaz. Now if you’re from the UK the name might strike you as being a bit distasteful, perhaps even offensive. Get over it, its just a random sound in most of the world.

Actually if you don’t like the name you can change it. Just edit the preferences.json file and tell it to report a different app name to the world. In fact there’s lots you can change about Spaz, and that’s why I like it.

Most of the changes I wanted to make were simple and could be done by editing the user.css file. Through css I changed the colours so they were a bit more like the old client I was used to, and prettied up the borders of each tweet by making them rounded.

For more advanced changes you can write your own theme, and if that’s not enough for you then download the source code and make your own version of Spaz!

Download my my user.css file to make your Spaz look like mine.

beerandnoodles posted a photo:

beerandnoodles posted a photo:

beerandnoodles posted a photo:

You’re importing some data into MySQL and using LOAD DATA INFILE so you can do a SHOW WARNINGS afterwards. Every line of your import file has a warning:

| Warning | 1262 | Row 1 was truncated; it contained more data than there were input columns
| Warning | 1262 | Row 2 was truncated; it contained more data than there were input columns
…etc.

The fix is easy, and you’d think of it eventually, but you thought you’d try google instead. So I’m posting this so that when you do there is something to find. There wasn’t much when I looked.

It’s the line endings. MySQL isn’t getting what it expects, so specify the format of the file using LINES TERMINATED BY ‘\r\n’ or whatever is appropriate for you:

‘\r’\n’ for files that came from Windows systems
‘\r’ for files from VMS
‘\n’ for every other source.

Hope that helps.

Shared by Alan

Apple come up with the most ridiculous reasons to keep competing software out of the hands of their users.

Then, says Crosby: “We were rejected for objectionable content. They provided screenshots of the words ‘shit’ and ‘fuck’ showing up in our dictionary’s search results. What’s interesting is that we spent a good deal of time making it so that you must type vulgar words in their entirety, and only then will we show you suggestions in the search results. For instance, if you type ‘fuc’, you will not see ‘fuck’ as a suggestion. This is in contrast to all other dictionaries we’re aware of on the App Store (including Dictionary.com’s application), which will show you ‘fuck’ in the search results for ‘fuc’, ‘motherfucker’ for ‘mother’, etc.
This is an auto import from my Google Reader shares. Read more at the original site.

Shared by Alan

Who needs an alarm? I want one of these!

In artist FilthyLuker’s street installations, giant inflatable tentacles emerge from buildings and vehicles, creating the sense that a monstrous kraken or Lovecraftian horror is trapped inside.

FilthyLuker creates whimsical sculptures and installations, with pieces that include anthropomorphized trashcans and easy chairs, adding eyeballs to bushes and trees, and giant banana peels placed in the middle of the road. His “Octo” installations are perhaps the most inspired, offering all the fun of a B-movie with none of the property damage.

FilthyLuker’s DeviantArt [via WebUrbanist via Neatorama]

Octopied Building

This is an auto import from my Google Reader shares. Read more at the original site.