Well, if that's all it is, then great. I tend to overblame myself. I was sure it was me.

Well, Joe Celko forewarded the book. I would have thought...

But this works (a subsequent lesson)....and the only difference is the SELECT seq=IDENTITY(int). "This approach uses the IDENTITY() function with SELECT...INTO to add an identity column to the work table....

Reiterating problem. This works:

--DDL for creating and populating staff table

create table staff(employee int primary key, employee_name varchar(10),

supervisor int null references staff (employee))

insert staff

values

(1, 'groucho', 1),

(2, 'chico', 1),

(3, 'harpo',...

OK, I'll check these links out but in the end I know I will come around to wanting to understand KH's chapter. His code the 'long way' works, but...

Yes, I think I read on a post elsewhere that if the servers go up and down often, might need to build an application to maintain counter numbers throughout the...

Thank you Lowell, for your thorough answer.

Yes, I did recognize that your query provided counters, and that is what I was after.

And now I can see that the UNION...

Hi Daglugub, sorry I didn't see this sooner. Need to get notifications sent to a more often used email inbox, which I will do right way. But, I am...

what is difference between

user_seeks

user_scans

user_lookups

user_updates?

I was just coming back here to post that I'd found a query that retrieves last_user_seek, but no counters and Lo!

Holy Goodness, this is great. Thanks Lowell.

I do have...

googling dm_db_index_usage_stats or sys.dm_db_index_usage_stats. Thanks.

ooh man, you got my back. Thanks. would'a goofed with that in my next interview 🙂

Daglugub, how about this?

--DDL to set up environment

create table userassignments (id int, userid int, companyname varchar(50));

insert into userassignments

values

(1, '1', 'companyA'),

(2, '1', 'companyB'),

(3, '2', 'companyB'),

(4, '2', 'companyC'),

(5, '3', 'companyA'),

(6, '3',...

SQL_Enthusiast,

I double and triple checked the book and the WHILE query in my original post is a facsimile.

The where clause condition is slightly different, it excludes o1.supervisor<>o1.employee. ...

So, group by limits you to one aggregation, and PARTITION BY allows for multiple groupings. Thanks Dwain.

John Mitchell, I see that the solution works. It's nice. I understand CTE and 'PARTITION BY YEAR(OrderDate)' etc., but will you please explain why 'PARTITION BY 1'?