Row Count Reset On New Group 1

[MSGCR]

My feeble SQL skills have failed me on this one. I ned a row count on buildings per location to reset on a new location. Can this be done in MS SQL?

Example:
Location Building Row_Count
1 A 1
1 B 2
1 C 3
2 A 1
2 B 2

 

[donutman]

To combined the points of two threads:
That's why vongrunt hasn't written the FAQ...he's a perfectionist who won't publish until it's perfect.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[maswien]

If insert won't work, then maybe following SQL will do the job:

select b1.location, b1.building, count(*)
from
buildings b1
inner join
(select location, building
from buildings
group by location, building) b0
on b1.location = b0.location and b1.building >=b0.building
group by b1.location, b1.building
order by b1.location, b1.building

 

[donutman]

Because the set-based method of data manipulation is so different from the sequential processing of the past (present in cursors)

That gets me on the .
I don't think it has anything to do with "sequential processing". It's just that the set-based statements can be understood "as a whole" and the entire solution optimized, i.e. every row has to be read sequentially no matter what approach you use. With cursors there's no way for the optimizer (MSSQL Server developers) to know what you're plan is, so their code becomes inefficient.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[maswien]

I think cursor is good for very complicated search or update when a SQL statement won't do the job, but a SQL can be very complicated too, so there are less chances the cursor is needed.

 

[donutman]

cursor is good for very complicated search or update when a SQL statement won't do the job

When exactly is that?
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[ESquared]

It IS sequential processing. It is a mental conceptual model about doing THIS thing to EACH row, ONE at a time. Never mind that the query engine has to do exactly that, the way the engine is addressed differs. People who want to use cursors often don't understand how set-based could possibly even work. How many times have you seen questions where people ask about updates or inserts for "more than one row at a time?" It would be so easy for them to fall into the cursor trap, because it has the feel of the For-Next loop. The better conceptual model is the set operation.

Yes, there are times when cursors are unavoidable, but oh well.

 

[maswien]

For example, you need get a resultset from a query, then go through each row of the resultset and pass them to a stored procedure, this stored procedure may be used as send email to customers.

 

[donutman]

ESquared, I'm talking about how the cpu works, not how you think about the solution. Set-based reasoning is good to understand because SQLServer has optimized the solution of those set-based problems. But the actual assembler or C code used by the programmers must still access the rows one at a time.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[ESquared]

Yes, it still must access the rows one at a time. But there's a world of difference between using a cursor to fetch each row and handing that to the DB engine, and giving the DB engine a bunch of work to do where it fetches each row one at a time.

Why are we even having this conversation? You hate cursors.

 

[maswien]

I think there are two kinds of resources: tables, functions are internal resources for a database; while other resources outside the database which can be accessed by extended stored procedures will be the external resources.

When dealing with each single row of the resultset need internal resources, I guess SQL Statement will be fine; If external resources are needed, then cursor.

 

[ESquared]

That's certainly one scenario where a cursor can be required... sending email, for example.

-------------------------------------
• Every joy is beyond all others. The fruit we are eating is always the best fruit of all.
• It is waking that understands sleep and not sleep that understands waking. There is an ignorance of evil that comes from being young: there is a darker ignorance that comes from doing it, as men by sleeping lose the k

 

[SQLSister]

I think cursor is good for very complicated search or update when a SQL statement won't do the job

Updates are exactly one thing that should never be done using a cursor. I will agree with the email example or other functionality using something outside SQL Server or even when wanting to run system sps over a group of tables.

But data manipulation including any select, delete, update or insert should always be done using a set-based command.

As esquared pointed out, if someone doesn't learn how to do it using the simpler examples, they will use a cursor for the more complex things which involve lots of rows becasue it is the only methodology they know. It is amazing to me how many people seem unaware that you can join to other tables in an insert, update or delete statment.

Questions about posting. See faq183-874

 

[maswien]

Following thread which relates to update will be another good example to use cursor:

http://www.tek-tips.com/viewthread.cfm?qid=971614&page=1

 

[ESquared]

mjia,

The actual update being performed in that thread is done set-based... the cursor is done at the table level.

In any case, I already suggested in that thread that it would be more efficient to just use search-and-replace in another program. The cursor and dynamic SQL method is overly complicated.

-------------------------------------
• Every joy is beyond all others. The fruit we are eating is always the best fruit of all.
• It is waking that understands sleep and not sleep that understands waking. There is an ignorance of evil that comes from being young: there is a darker ignorance that comes from doing it, as men by sleeping lose the k

 

[maswien]

I know that it's easy to find a example that can be implemented using SQL statement, but it's hard to find an good example that cursor is needed. Usually people who post the questions are not very good at SQL, so in most the cases the cursor they already put in their code can be avoided, but in the real world, I'm sure that there are quite few circumstances that cursor can't be avoided.

 

[SQLSister]

I've written exactly one cursor (the email example), so no I don't buy this. In fact, the developers here are not allowed to use cursors and never has one come to me and showed me where he absolutely had to use one.

Questions about posting. See faq183-874

 

[ESquared]

The one place I use a cursor is on my own table variable that never has more than four items in it. Then again, I wrote this about five months ago and I know more now than I did then...

-------------------------------------
• Every joy is beyond all others. The fruit we are eating is always the best fruit of all.
• It is waking that understands sleep and not sleep that understands waking. There is an ignorance of evil that comes from being young: there is a darker ignorance that comes from doing it, as men by sleeping lose the k

 

[maswien]

For me, I actually never use cursors in my job, not because I don't like it, but not necessary to use it. How do you explain the thread:

http://www.tek-tips.com/viewthread.cfm?qid=971614&page=1

Esqured said the cursor is used in the table level, but still it's used and isn't avoided at all, here we are talking about that cursor is an alternative for SQL statement but not anything else like word etc.

 

[donutman]

Yes, it still must access the rows one at a time. But there's a world of difference between using a cursor to fetch each row and handing that to the DB engine, and giving the DB engine a bunch of work to do where it fetches each row one at a time.

Why are we even having this conversation? You hate cursors.

To address your last point first:
Because I've heard the case made for set-based programing explained in a way that isn't correct. That set-based solutions are faster than sequential processes. That statement hides the truth. And the solution for the original post is a perfect example of where sequential processing is in fact faster, provided you compare the techniques in a more fair manner. By that I mean, allow a C# programmer to have access to the same tables (base table and index) and he could easily write a routine that would crush the "set-based" approach. The fastest solution after all is to read the file one index at a time and make the necessary comparisons to increment or reset the counter. However, when you use SQL Server's cursor capability it is no where near as efficient as doing the more complicated process of joining the table to itself or doing a correlated subquery. I've heard set-based solutions bantered about as being superior because they [magically] do operations in sets rather than one at a time. Which is simply wrong besides being hyperbole.
Your first paragraph is restating my position that SQL Server has merely (no simple task) optimized what we as humans have expressed as a set-based (completely self-contained) request.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[ESquared]

Fair enough! From hereon out, when I say set-based I mean the logical construct of the query, not the actual work done under the hood by the DB engine.