November 02, 2010

SQL issue using GROUP BY and ORDER BY together

I am having an issue with SQL not behaving as I expect it to. (MySQL 5.1.41 although it shouldn’t be relevant.) I am hoping I’ve missed something really obvious but I just can’t see it at the moment, hence blog post.

I am dealing with submissions to a node with some data.

CREATE TABLE test (time int, nodeid int, data varchar(50));

| Field  | Type        | Null | Key | Default | Extra |
| time   | int(11)     | YES  |     | NULL    |       |
| nodeid | int(11)     | YES  |     | NULL    |       |
| data   | varchar(50) | YES  |     | NULL    |       |

time is the primary key for purposes of this explanation
nodeid is a foreign key
data is some information associated with a submission

INSERT INTO test (time, nodeid, data) VALUES (1, 1, 'ONE');
INSERT INTO test (time, nodeid, data) VALUES (2, 2, 'TWO');
INSERT INTO test (time, nodeid, data) VALUES (3, 3, 'THREE');
INSERT INTO test (time, nodeid, data) VALUES (4, 4, 'FOUR');
INSERT INTO test (time, nodeid, data) VALUES (5, 1, 'FIVE');
INSERT INTO test (time, nodeid, data) VALUES (6, 2, 'SIX');
INSERT INTO test (time, nodeid, data) VALUES (7, 1, 'SEVEN');
INSERT INTO test (time, nodeid, data) VALUES (8, 3, 'EIGHT');

| time | nodeid | data  |
|    1 |      1 | ONE   |
|    2 |      2 | TWO   |
|    3 |      3 | THREE |
|    4 |      4 | FOUR  |
|    5 |      1 | FIVE  |
|    6 |      2 | SIX   |
|    7 |      1 | SEVEN |
|    8 |      3 | EIGHT |

Node 1 has three submissions: ONE, FIVE, SEVEN
Node 2 has two submissions: TWO, SIX
Node 3 has two submissions: THREE, EIGHT
Node 4 has one submission: FOUR

We want to show the submissions, most recent first:

SELECT time, nodeid, data FROM test ORDER BY time DESC;
| time | nodeid | data  |
|    8 |      3 | EIGHT |
|    7 |      1 | SEVEN |
|    6 |      2 | SIX   |
|    5 |      1 | FIVE  |
|    4 |      4 | FOUR  |
|    3 |      3 | THREE |
|    2 |      2 | TWO   |
|    1 |      1 | ONE   |

But now we want to show only the most recent submission for each node, so we try:

SELECT time, nodeid, data FROM test GROUP BY nodeid ORDER BY time DESC;
| time | nodeid | data  |
|    4 |      4 | FOUR  |
|    3 |      3 | THREE |
|    2 |      2 | TWO   |
|    1 |      1 | ONE   |

That’s not what we want! The most recent submissions are 8, 7, 6 and 4.

Among other things I have tried:

SELECT time, nodeid, data FROM test GROUP BY nodeid ORDER BY MAX(time) DESC;

(returns first submissions ordered by last submission time)

SELECT MAX(time), nodeid, data FROM test GROUP BY nodeid ORDER BY MAX(time) DESC;

(returns first submission data but last submission time)

SELECT time, nodeid, data FROM test GROUP BY nodeid HAVING time = MAX(time) ORDER BY time DESC;

(returns only one result)

SELECT time, MAX(time) timemax, nodeid, data FROM test WHERE time = timemax GROUP BY nodeid ORDER BY time DESC;

(error: can’t use aggregation functions in WHERE clause)

Is there anyone who can help me out and suggest how to get the result set I need?

June 12, 2010


I spent last weekend at TIGJam UK3, a gathering of independent game developers held in Cambridge. I had a really great time, hanging out with some cool people and making a bunch of games.

At the centre of the event was a ridiculous number of three hour challenges: I ended up making eight games over the weekend. They’re not online yet but you can have a screenshot from each of them:

You can play other people’s games from the weekend at the Cambridge Friendship Club website.

Compared to TIGJam UK2 in January, the atmosphere was perhaps more hectic. There I took part in three game jams, each time working with other people. The rest of time I spent working on existing games in a fairly relaxed manner.

This time around, I started eight new games and didn’t team up with anyone (although I did reuse some old art from past projects). With three game jams in a day, each generally three hours long, there was only about an hour between one challenge finishing and the next starting up (time normally spent finishing up the first project).

On the other hand, eight games is more than three games, so the system certainly works. In two weeks time I’m going to World of Love which is going to be followed by another weekend of game development. I’m very much looking forward to it!

March 25, 2010

Avoiding the Visual C++ Redistributable Package

Visual Studio is a great IDE. Unfortunately the C++ compiler is horrible. Among other things, under the default settings a compiled application won’t actually run out of the box on a large number of computers.

This is because of the “Visual C++ Redistributable Package” which must be installed to run applications compiled from Visual Studio. This behaviour is nonsensical to me, but nevertheless it is there and must be worked around.

There are the solutions that I know of:

Don’t use the Visual C++ compiler

This is my solution, but then I do my development from Linux anyway. MinGW is an implementation of the GCC compiler suite for Windows, and creates executables that don’t rely on invisible Microsoft dependencies.

It may or may not be possible to use an alternative compiler from within the Visual Studio IDE. I can’t find any references for it, but I have heard (though not tested) that it is possible to set up a project that uses a makefile to build.

Code::Blocks is probably the best alternative IDE to Visual Studio. They also provide a download package which includes MinGW.

Include DLLs with executable

This is what Microsoft calls a “private assembly” and what I call “putting the DLLs in the application directory”.

The files you need will be in C:\Program Files\Microsoft Visual Studio 9.0\VC\redist\x86\Microsoft.VC90.CRT\ (or equivalent) on a computer with Visual Studio installed. As well as the three DLL files msvcm90.dll, msvcp90.dll and msvcr90.dll, you will also need the manifest file Microsoft.VC90.CRT.manifest.

Copy these files to the directory containing your executable and include them with your download.

Use an installer

I’ve not done this myself, and for a lot of the people I expect to read this, it will be overkill. So not much useful advice to give here, other than to read this blog post to see the options. Does anyone have a “how to create a ridiculously simple installer” guide?

Get end users to install the Visual C++ redistributable package

For Visual Studio 2008 SP1 this is downloadable here: other versions are similarly available from the same site.

Once installed, your application will run on that computer, but obviously this is not very convenient for the user.

Statically link the Visual C++ libraries into your executable

This is potentially problematic if you are using other libraries: all the DLLs you are using must have been compiled under the same settings. For example, the precompiled builds of SDL for Visual Studio are apparently incompatible with this. So you may have to get your hands a bit dirty to get it to work (or you may not). To do this, go to:

Project -> Properties -> Configuration Properties -> C/C++ -> Code Generation -> Runtime Library -> change to “Multi-threaded [Debug]” instead of “Multi-threaded [Debug] DLL

You need to change this in both debug mode and release mode.

Microsoft strongly recommends you not do this, as security issues cannot be patched if the library code is statically linked into your executable. (Sidenote: interestingly, putting the DLLs in the application directory doesn’t have this problem, because if you have a more recent version installed that will be used instead.)


Deployment section on MSDN for VS2008
Visual C++ blog on the subject
Blog post covering this from a more installer-y perspective
More information about static linking issues

February 06, 2010

Visual Studio + STL containers = poor performance

A fun fact that I discovered when developing my physics engine Large Polygon Collider last year: the STL containers that Visual Studio uses are by default offensively slow.

There are three solutions I know of:

1. Use another compiler such as GCC

MinGW is an implementation of the GCC compiler suite for Windows.

It may or may not be possible to use an alternative compiler from within the Visual Studio IDE. I can’t find any references for it, but I have heard (though not tested) that it is possible to set up a project that uses a makefile to build.

Code::Blocks is probably the best alternative IDE to Visual Studio. They also provide a download package which includes MinGW.

Another advantage of switching compiler is that you avoid another issue of Visual Studio: you need to ensure that someone running your application has the Visual C++ Redistributable package installed. But that is another rant for another time.

2. Use another STL implementation such as STLPort

STLPort is an alternative STL implementation. Performance-wise, it should beat the Microsoft implementation, even after fixing that as described below.

3. Change the idiotic Visual Studio behaviour

There are a couple of obscure macros that you must define to disable the runtime safety checks which cause the agonising slowness:

#define _SECURE_SCL 0

You should set these in the project settings, under pre-processor definitions (and make sure to do it in both debug and release mode). Don’t ever do it from a header file unless you’re certain that file will always be included first. So the new set of definitions should look something like this:


I also put this in an always-included header file:

#ifdef _MSC_VER
    #if( _SECURE_SCL != 0 )
        #pragma message( "Warning: _SECURE_SCL != 0. You _will_ get either slowness or runtime errors." )

        #pragma message( "Warning: _HAS_ITERATOR_DEBUGGING != 0. You _will_ get either slowness or runtime errors." )

Which will give you warnings if the macros aren’t defined.

The reason those warnings talk about runtime errors is because of the catch related to this solution:

If you link with another object/library with those macros defined differently (i.e. the default), you may get runtime errors. The gorey details as descibed in this comment are as follows:

These are the rules that you must follow when modifying _SECURE_SCL and _HAS_ITERATOR_DEBUGGING:

  1. They must be consistently defined within each translation unit (i.e. a source file and all of the header files that it includes). For example, you can’t include a Standard header, alter _SECURE_SCL or _HAS_ITERATOR_DEBUGGING, and include another Standard header.
  2. Translation units that are linked into the same binary (executable or DLL) must have identical definitions of _SECURE_SCL and _HAS_ITERATOR_DEBUGGING. For example, linking one .obj with _SECURE_SCL==0 to another .obj with _SECURE_SCL==1 into an .exe or .dll is bad. For another example, linking several .obj files with _SECURE_SCL==0 into a .lib, and then linking an .obj with _SECURE_SCL==1 against that .lib into an .exe or .dll is bad.
  3. Binaries that pass STL objects between each other must have identical definitions of _SECURE_SCL and _HAS_ITERATOR_DEBUGGING. (They must also be both retail or both debug.)
My (not necessarily 100% accurate) summary of those rules:

You must have the same settings across your entire project and in any libraries you statically link to. Dynamically linked libraries should be fine unless they use STL containers in their public API.

The error message you get if these rules are broken will be beautifully unhelpful and even misleading: so be on your toes.


MSDN page on checked iterators and _SECURE_SCL
MSDN page on debug iterators and _HAS_ITERATOR_DEBUGGING
Blog post discussing the implementation of _SECURE_SCL and _HAS_ITERATOR_DEBUGGING. Comments discuss the runtime error issue.
Video about STL Iterator Debugging and Secure SCL

December 19, 2009

Topsy Turvy

Writing about web page

Last weekend I made the game Topsy Turvy for Ludum Dare 16. It’s a one-button platformer with gravity switching, and it’s HARD. Here’s the post-mortem:

What went right:

  • The concept. I’ve had the idea in my head for a while, and I’m very glad that I used the competition as an excuse to make it rather than forcing myself to think of a game idea inspired by the theme. And the game ended up pretty much how I imagined, so that’s good too.
  • Inkscape as level editor. AS3 has some rather lovely XML-parsing abilities, so reading the SVG file was surprisingly simple. It’s very much hard-coded to the specific output that (my copy of?) Inkscape generates, but it should be fairly easy to fix if it ever stops working.
  • Abstract graphics. I am not an artist, so I decided to save time and just draw everything out of lines. I think the results fit the game fairly well, even if they’re not actually good.

What went wrong:

  • The goal. I added the collectables to provide an incentive to get to the more difficult areas and also as extra landmarks for getting your bearings. Unfortunately, with time running out and no win conditions implemented, I made the decision that you would win if you could collect all of them. In hindsight, I should probably have added a level exit instead.
  • Difficulty. The game is ridiculously hard. I knew I wanted to have some areas which would be tricky to get to, as a challenge, but when the goal became “collect everything”, those areas suddenly became non-optional.
  • First day motivation. I wanted to have all the basic game mechanics done by the halfway point, but I was just procrastinating like crazy. I’d come to the conclusion that it just wasn’t technically interesting enough to hold my attention, but then on Sunday morning I added death and respawning. Suddenly my game idea was in front of me and I could start constructing devious routes through the level, and I spent the rest of the day excited by it.
  • The name. It actually changed name twice between starting and submitting, and I’m still not really happy. Currently thinking about maybe renaming it “Jump-Zap-Flip”.

Lessons learnt:

  • Get death/respawn implemented earlier in future
  • Think of a win condition as part of the design process
  • Don’t make ridiculously hard challenges required to complete the game

August 28, 2009

Faster Flash compilation when using mxmlc

Writing about web page

So the Flex compiler mxmlc is pretty cool; it allows you to make Flash applications with a text editor compiling from the command line (as all programming should be done).

Unfortunately, it’s horrifically slow. The fcsh tool (also in the Flex SDK) keeps everything in memory for much faster compile times, but you need to keep it running and the shell is rubbish.

Solution: fcsh-wrap is a wrapper for fcsh to give it a better user interface and allow you to run it from make. It runs as a daemon process which manages a fcsh process.

Based heavily on this original code but with some bugfixes and improvements.

1) Edit the file to point to your copy of fcsh (if it’s not in your $PATH)
2) Run fcsh-wrap instead of mxmlc, with all the same arguments.

  • Python
  • The Flex SDK
Known issues:
  • Output goes to the terminal that you start it running on, even if you’ve closed that window
  • Almost certainly won’t work under Windows, but if you’re running Windows you should probably be using FlashDevelop anyway

January 16, 2009

And so begins 2009

I have a problem, which is that if I don’t write things down I forget them. Be they amusing, meaningful or important, before long I struggle to remember details and soon after that they’re gone.

I also have a secondary problem, which is that I don’t have the time to write things down.

Put together, these might seem like an insurmountable obstacle, but I am of course equal to all things. Solution: I shall use Twitter to post messages summarising my day. Call it a two-weeks-into-the-new-year resolution if you like (personally I wouldn’t).

Events requiring more than 140 characters will be typed up to a blog entry, but they will be short blog entries, such that they can still be written before sleeping. If Twitter’s character limit annoys me too much, I may post everything in blog format, but I’m unsure about that.

I have tried this a couple of times before (originally prompted by the film Into the Wild) but I hope that by posting daily rather than saving the entries up for one large (and mostly boring) post in the future, I will manage to keep it going for longer.

For completeness, here are some entries for 2009 up until this point:

Dec 31: Saw Yes Man with Gnus; played Monopoly until the new year then tried to play Cheat with a confusing pack of cards.
Jan 1: Spent all day not packing but somehow got to bed before 2:00.
Jan 2: Up at stupid o’clock to get to the train station; travelling to skiing with a four hour stop-off in Paris.
Jan 3: Arrived in Val Cenis around 9:00 am and spent the day teaching Mairead and Hennell to ski.
Jan 4: Skiing.
Jan 5: Skiing.
Jan 6: Spent all day in the apartment writing an essay.
Jan 7: Skiing.
Jan 8: Skiing.
Jan 9: Last day of skiing.
Jan 10: Travelling home. Missed our flight due to Italian train doors that wouldn’t open (twice!).
Jan 11: Spent all day on internet. [Amusing read]
Jan 12: First day back at uni.
Jan 13: Six straight hours of lectures followed by a project meeting.
Jan 14: Slept through an exec meeting and was generally unproductive. Played Perudo in the late evening. [Game]
Jan 15: Joined Started doing Rob’s coding challenge, which is to create a C++ program without being able to compile it.

Let the daily dullness commence.

January 15, 2009

4 months ago

Another entry I started a while ago and didn’t finish:

Sep 11: American Beauty
Sep 12: Comedy
Sep 13: Chris & Pete’s 10th year anniversary
Sep 14: Miss Pettigrew Lives for a Day
Sep 15: Judge Dredd
Sep 16: Coventry
Sep 17: Failed to apply common sense and needed help turning the oven on and when resetting the circuit breaker
Sep 18: Went round to Jen’s house and met Amanda
Sep 19: Hanging out watching Red Dwarf and Youtube
Sep 20: Warwick Game Design stall at Open Day
Sep 21: Joey cooked a large meal for everyone
Sep 22: Met with Blitz—went pretty well
Sep 25: Played Mao
Sep 26: Did WGD website stuff that I should have been doing all week
Sep 27: Assassins’ Guild; met Phil & parents

6 months ago

An unpublished entry I found from July:

June 4: Larked around in Maths late at night
June 5: Last exam
June 6: Found that ASCII strings are unlikely to appear in the hexadecimal representation of pi and watched
June 7: BBQ and tree climbing;
June 8: Cycled to and from Kenilworth for a picnic and tree climbing.
June 9: Met up with Louise; watched the Bourne Identity
June 10: Juggling in the sun;
June 11: Talked about fourth year project, looked into physics determinism, solved billing issues for Z-I, and thought about (and decided not to) installing Trac.
June 12: Playtesting at Blitz. Also started writing my talk on physics and collision detection.
June 13: Worked on
June 14: Jon’s stag do – frisbee, tree-climbing, go-karting, BBQ
June 17: Set up a Redmine installation for the fourth year project. Lots of discussion about same; no conclusions.
June 18: Spent most of the day in the Learning Grid but did practically nothing. Console social.
June 20: My talk on physics and collision detection
June 23: Power tools at Ellie’s house
June 24: Drove to the east coast, swam in the sea and watched the sunrise
June 25:
June 26: WSAF game showcase; Sylvia’s house in the evening
June 28: Jon and Ellie’s wedding
June 30: Clearing Jon’s room and moving his massive amounts of stuff to his house for him
July 3: Last minute packing and cleaning; came home
July 4: Made a cake; played Brawl; read
July 5: Went to Guildford to see David Race and the Prydes
July 7: Birthday
July 9: Tidying and sorting
July 10: Worked on Mindbender
July 11: Bowling with Gnus
July 12: Starting work on a new flash game
July 13:
July 14: Hair cut; physics
July 15: Grandparents in Newbury
July 16: Read and realised this problem existed in my physics engine
July 18: To London on train, watched Octopussy for some reason
July 19: Back to Bristol to get Dad’s wallet; then back to London after Chris’s house-warming party
July 20: Plane to New York

October 31, 2008


No money. 36 hours. As far away as possible.

It starts tomorrow morning: where will we end up? Well in the best of all worlds we would smash all previous records and find our way to New Zealand somehow, and I see no reason not to work under that assumption…

You may or may not be able to follow updates at Twitter and/or my Facebook profile, depending on what Orange decides to charge me for texting from abroad and whether I decide that’s worth it.

If you wanted to sponsor us you could do so here.

New blog location

After a hiatus of several years, I’ve started blogging again at

My website

Looking for more information about Alan Hazelden? Follow me on Twitter or go to my website.

October 2019

Mo Tu We Th Fr Sa Su
Sep |  Today  |
   1 2 3 4 5 6
7 8 9 10 11 12 13
14 15 16 17 18 19 20
21 22 23 24 25 26 27
28 29 30 31         

Search this blog

Blog archive


Most recent comments

  • Knife game: No actual cutlery involved. A single player starts with the "knife" by clasping his or h… by Noyb on this entry
  • My friends and I have taken to this one very silly game lately that's probably altogether too insula… by Ian S. on this entry
  • Great, thanks for those! That first one I would know by the name of Wink Murder, usually with a sing… by on this entry
  • I don't know the names for these games, so I'll make them up: Sniper. Randomly choose who is the sin… by zep on this entry
  • I recommend checking out Project Sprouts from Luke Bayes. It makes obtaining and configuring the fla… by Duncan Beevers on this entry


RSS2.0 Atom
Not signed in
Sign in

Powered by BlogBuilder