Rob Farley : cte

Set-based approach to finding consecutive records

Rob Farley — Wed, 17 Jan 2007 00:15:00 GMT

I'm sure I've written about this before, but it comes up so regularly in the newsgroups that I thought I'd write an answer here.

A table (probably an audit table) exists with a field that stores a date signifying when a change was made. You need to return a result-set which has a row for each period of time between modifications.

For example:

CREATE TABLE QtyAdj ( .... , ModDate DATETIME, Qty INT )

--where I haven't listed all the columns - there should obviously be a primary key on this. In fact, I'll assume there is a field called ID which is unique.

What we want is:

with modnums as (select *, row_number() over (order by moddate, id) as rn from qtyadj)
select m_this.moddate, m_next.moddate, m_this.qty
from
modnums m_this
left join
modnums m_next
on m_next.rn = m_this.rn + 1

Which uses row_number() within a table expression to give a unique number to each row ordered by the date. You can then join between two copies of the table expression (love CTEs for that), to get your consecutive records in a single row of your result-set.

Faster binary converter - where do your solutions come from?

Rob Farley — Mon, 23 Oct 2006 23:53:00 GMT

Mitch Wheat is a great guy. He really is. And he's clearly a mathematician.

I mean, I did a half-major as part of my BSc, doing a handful of 3rd year Pure Maths subjects (which I got HDs in too - I wasn't just sleeping during class), but Mitch... he thinks like a mathematician. You can tell when he comes up with a solution like this for converting numbers to binary. Makes me wonder if this is something he came up with, or something he read in a book. Knowing Mitch, he came up with it himself. I'm in a bit of awe here. I feel like it's the kind of solution that I could've come up with once upon a time, and hopefully could still given the right colleagues and a whiteboard. :)

Let's come back to the 'solution read in a book' thing.

I remember sitting in a user-group meeting in Redmond in March. Itzik was speaking. He was talking about the use of CLR in SQL, and came across the Product aggregate function. There is no product() in T-SQL, and a lot of people have considered that it's a good candidate for CLR, since you can make a user-defined aggregate function this way. Itzik's lines were something like "And I was sure there was a good way of doing this without having to use CLR, so I got reading my maths books." He mentioned some of the names that I have a vague recollection of from my university days (which I have forgotten again so won't write here), and then said that he came across log(a*b) = log(a) + log(b). And of course, almost everyone in the room had light-bulbs appear.

I wasn't so much inspired by the fact that "here's this great way of solving the product question", but rather "here's a way of solving some miscellaneous thing, where the answer comes out of a maths book." And now I've found Mitch providing me with the same hunger to academise (that's not a word, but you know what I mean).

I nearly got into academia. Having done uni, I had offers to do a PhD. But I had a good job offer, and studying didn't seem like it was going to pay the bills. A large part of me would've liked to have done. I remember being a research assistant for Professor John Crossley at Monash, helping to write code (in CAM/L, using a Tcl/Tk interface) to try to convert mathematical proofs into computer programs (the idea being that if you can prove - using really fundamental principles - that for all x, there exists a number y that has particular properties, then that proof should be able to be converted 'automatically' into a program which will find that y). I found that fascinating. I don't know whether the theory ever got really proven, but I found academia really interesting.

And of course, in the 'real' world, the uses of academia are somewhat doubted. It was good to see a locknote session at Tech.Ed (AU) this year about some of the image processing stuff that Microsoft Research Labs are working on. But I'm also really curious to know about algorithms for solving interesting problems.

Simple recursive CTE

Rob Farley — Fri, 20 Oct 2006 09:26:00 GMT

I like CTEs. Mostly because it lets me have a derived table that I can refer to multiple times. I don't often use the recursive features of it. But when I do, I repeatedly feel amazed at the power available.

Today I had a challenge to produce a nice SQL way of converting integers to binary.

So I pulled out the CTE. :)

with ctebins as
(select num as num_orig, num as working_level, cast('' as varchar(max)) as binval
from nums_bin
union all
select c.num_orig, c.working_level / 2, cast(c.working_level % 2 as varchar(max)) + c.binval
from ctebins c
where c.working_level > 0
)
select num_orig, binval
from ctebins
where working_level = 0
;

What this is doing is to populate the top level of the CTE with a bunch of numbers from nums_bin, along with a blank string. I know I don't have to use varchar(max) - it's not going to get that big after all...

Then it fills the CTE with rows using recursion. I keep the original number (to use later), and keep cutting it in half to get the binary number out. Then I query the full CTE for the last level of recursion - when my working number got down to zero.

It works nicely, and it's very fast on a big set of numbers.