No doubt! The ROW_NUMBER function is very useful.

Also, I updated my article

http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx

Update on this:

I received two replies. One said that CREATE TABLE w/ IDENTITY then INSERT w/ ORDER BY is, indeed, guaranteed. Another said that it's only guaranteed if...

Did you look at the example I posted?

Replace the 1 in the 'TOP 1' in the derived table with the (n) - 1 row you want and the 3 in...

Noeld,

What kinds of things do you use it for? I'm quite curious, as I've never had occasion to use it -- but it's brought up fairly regularly by users...

I just recently had that conversation about it being fixed in SQL Server 2005, so I'm a bit confused by Ben-Gan's response there. I've posted a request to the...

PW, you're correct -- the IDENTITY is not guaranteed to come out in any specific order. But this isn't true only on SELECT INTO -- it's also true if...

I don't really care about implicit conversion in this case -- much worse practice is the date format the author of this question used...

'01/01/2005' might have only one possible meaning......

Okay, I just posted a UDF to help solve this problem:

http://sqljunkies.com/WebLog/amachanic/articles/PatternSplitString.aspx

... To use it for this problem, you would have to cursor/loop through the table for each row you...

So how do we sign up to get that? I've never seen MCP magazine either, for that matter -- apparently I'm missing out on my benefits!

What is a "max sum"?

I think the OP wants the 2/3/4 highest sums based on some grouping, in which case I assume that the grouped column(s) would also be necessary...

The problem with most split functions is that they can handle only a single character, e.g. ',' -- I think you would have to modify it to handle a pattern...

Check out this link for my findings on that:

http://sqljunkies.com/WebLog/amachanic/archive/2004/10/22/4743.aspx

You cannot supress that warning. How about not creating the temp table with such a large row size? Use TEXT/NTEXT/IMAGE columns if necessary...

Maybe?

Sharing some code might shed some light on whether or not it can be optimized; but have you considered using DTS for this?

Don't forget, Frank, you could also do it with a cursor 🙂