Try this, using SQL2005.

create table #tmp (id int, oppcode int, partsamt int)

insert into #tmp values (1,891,100)

insert into #tmp values (2,971,50)

insert into #tmp values (3,null,75)

insert into #tmp values (4,654,500)

select * from...

Oh, sorry - I misread what you wanted to group by.

The key here is just to create a query that gives you that value for 'Opp Code'. Bear with me...

Does this work for you:

declare @tablename varchar(256)

set @tablename = 'Inventory'

-- First let's fetch the id column.

-- We could have done this in our cursor, but it's nice to separate...

You're trying to write an update query without using column names?

You could generate the SQL you want into a string, and then execute that. It's not a nice way of...

If you already have something that gives you 57 on records 1 through 4, then a normal sum query should do the job, right?

As an exercise, it's fun to write the code to make a database do this kind of work. In reality though, you wouldn't use a database for it, because a...

Noticed the large number of posts on this, and got curious.

Here's an iterative (non-recursive) search. It does it breadth-first, so it finds the solution that uses the smallest number...

Your WHERE clause means that you only get the rows in tableA that don't match something in tableB. Since you're being MORE RESTRICTIVE in finding matching rows in your second...

This is one of those scenarios where I think I prefer the definite correctness. And for which I'm very glad about the 'row_number()' function in SQL2005.

When I wrote: "To me it looks like a ranking function on the 'id' field", I was right, wasn't I?

It's basically...

select id, name, colid, row_number() over (partition by id order...

Not everyone.

Very pleased that 2005 has user-defined aggregate functions (which reminds me, I need to play with that some more), so that people...

Oh, I know there's no guarantee... but that's the thing with this shortcut. Personally, I hate it and would rather use a CURSOR! But that's just because I prefer correctness...

But that's why he put the index on the table, so that it would order it for him without changing the 'order by', right?

Let me see... (please excuse the obvious bits)

-- Insert into Test (id, name, Colid) Select Distinct id, name, Colid from dbo.SysColumns

Well, [id] is the table name, so this just populates...

Well, you could put a foreign key on Chris' table, so that the system won't let you delete that row out of the original table until you've deleted it.

If you...