Well, I felt clever anyways

[hyuga]

I pulled off some SQL voodoo the other day that I thought was pretty neat. I don't know, maybe this is obvious to someone with more database experience than I have, but I thought I would write it down so I remember it later. The problem, in simplified form, is something like this

Comments

[lucky_otter]

Shouldn't that be O(n^2)? A naive implementation certainly is, but maybe with query optimization it gets better.

[hyuga]

Well yes, it's up to the DBMS to optimize, with some hints of course. But true, it scales horribly for more than a few thousand rows. Fortunately, where it's in use we don't expect there to be more than a few hundred rows most of the time, and that's performing just fine.

[lucky_otter]

You could also use a variable in MySQL. e.g.:
SET @val := 0;
UPDATE Vals
SET Order = (@val := @val + 1)
ORDER BY Value;

To condense it into one statement, you'll need to use a subquery and join:
UPDATE hyuga
JOIN
(SELECT val, @num := @num + 1 rownum FROM hyuga, (SELECT @num := 0) temp ORDER BY val) sorted
ON sorted.val = hyuga.val
SET row_number = rownum;

You can't just do one top-level JOIN because you can't use ORDER BY in a multi-table UPDATE - at least in MySQL. Dunno about other DBMS.

Should be fast, but I haven't benched it.

This is a message.

[anonymous]

Hey, you still are a pretty hard man to get a hold of. Not sure if I should ever call or not, my schedule is always pretty wierd. It looks like I might be going to that con in baltimore if you're still headed up there as well. And as it turns out I'm going to be going to college. ? o_O ?

Um...so talk to you sometime in the next week or so.

Peace.

- Brandon

[darcenciel]

Hi! This has nothing to do with your post and actually has to do with Otakon instead, but this is the first public post I see on your LJ =P I was with ifotismeni at the Phoenix Wright panel on Saturday and she mentioned we just missed seeing you, which is too bad! (I don't know if you've seen her pictures - I was the Cid and/or Tifa in them from Final Fantasy

[hyuga]

Ah, FFVI eh? A fine, fine choice. I have long blonde hair, so I'd probably make a good Edgar without having to fuss with a wig. Setzer and Kefka are other possibilities.

Alas, most FFVI costumes are not trivial to do well, and I already have one cosplay commitment for 2009. Nor do I know yet whether or not I'll be going to Otakon next year. So it's far too early for me to be able to make a commitment. But keep me posted; I am interested.

[darcenciel]

I quite understand! I'm just amassing a list of interested parties right now. I have a definite Terra and Locke, a probable Celes, and then Maria is going as Kefka and I will be Shadow. I might also have someone as Sabin, but that's iffy at the moment.

Maria told me you have long and blonde hair, so I was thinking of you as either Edgar or Setzer!!

As far as the costumes themselves go, I have no problem making them myself for other people. I'm pretty good with cosplay (don't know if you saw Maria's photos from the weekend) so as long as I have a definite yes from someone, I can start planning.

Again, it's reaaally early so no rush or anything, but yay I'm glad you're interested, and I'll keep you updated!!

[hyuga]

Great. I just saw Maria's pictures and your Cid and Tifa costumes, which are quite nice. I remember seeing you as Tifa going into the bathroom at one point. I was standing around in a blue dress with a bandana over my face.

Anyways, if you're willing to help with the costume that's definitely a plus, though I'd want to work on it some too, or at least pay for materials if nothing else. I still don't even know if I'll be able to go to Otakon next year though, so yeah, like you said, it's early. Is it okay if I add you to my friends list to keep in touch?