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

 

[mrdenny]

Not automatically. You would need to write code to handle this.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[MSGCR]

I know. i jwant help writing the temp tables within my stored proc to do the count. or i believe it can be done with cursors, i just don't know how.

 

[mrdenny]

It can be done with a select statement within your stored proc that will be inserting the data.

create procedure ...
   @location int,
   @building varchar(10)
as
...Your code here...
declare @row_count int
set @row_count = (select max(row_count)+1 from table where location = @location)
insert into table
(location, building, row_count)
values
(@location, @building, @row_count)
go

Let me know if you have any more questions.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[MSGCR]

Anyone else? Please.

 

[donutman]

There is a way to do it without cursors and without creating a Temp table, but you have to have a primary key field in the table. Do you have one?
-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]

 

[MSGCR]

Yes. The building id is a primary key.

 

[donutman]

I assume that BuildingID isn't Location or Building from your 1st post? If so then it's not unique.
-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]

 

[MSGCR]

The building id will always be a unique id. the earlier post was just a simple count for display purposes.

 

[donutman]

We're going out for dinner. I'll try to figure it out before my wife pushes me out the door, so it may not be until about 10pm before I can post it (provided I can get it to work at all)! I know vongrunt and ESquared can do it in their sleep.
-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]

 

[donutman]

[Blue]SELECT[/Blue] T1.Location[Gray],[/Gray] T1.Building[Gray],[/Gray] RowCnt [Blue]FROM[/Blue] Table1 T1 
   [Blue]INNER[/Blue] [Gray]JOIN[/Gray] 
 [Gray]([/Gray][Blue]SELECT[/Blue] T3.Location[Gray],[/Gray] T3.Building[Gray],[/Gray] [Fuchsia]COUNT[/Fuchsia][Gray]([/Gray]T3.Building[Gray])[/Gray] RowCnt
    [Blue]FROM[/Blue] Table1 T2 [Blue]INNER[/Blue] [Gray]JOIN[/Gray] Table1 T3 
    [Blue]ON[/Blue] T2.Location[Gray]=[/Gray]T3.Location [Gray]AND[/Gray] 
       T2.Building[Gray]<[/Gray][Gray]=[/Gray]T3.Building
    [Blue]GROUP[/Blue] [Blue]BY[/Blue] T3.Location[Gray],[/Gray] T3.Building[Gray])[/Gray] T4
  [Blue]ON[/Blue] T1.Location[Gray]=[/Gray]T4.Location [Gray]AND[/Gray] T1.Building[Gray]=[/Gray]T4.Building
  [Blue]ORDER[/Blue] [Blue]BY[/Blue] T1.Location[Gray],[/Gray] T1.Building

-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]

 

[donutman]

BTW, the downside of this approach is that it is not efficient if there are a dozen Buildings for one location.
-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]

Assume your original table is buildings which has two column: location, building

Create another table blds:

select top 0 * into blds from buildings

then add a column to it:

alter table blds add ord int identity(1,1)

Populate it:

insert into blds(location, building)
select location, building from buildings
order by location, building


Then you can get the result by following SQL:

select b1.location,
b1.building,
b1.ord + 1
- (select min(ord)
from blds b2
where
b2.location=b1.location)
from blds b1

 

[ESquared]

Visit donutman's new thread, Visualizing the Row Counter Solution, for more on the join method of row-counting.

-------------------------------------
• 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

 

[donutman]

Mjia, you can't rely on an Order By statement in an Insert statement. If the number of rows inserted is large enough, the optimizer will sort the rows in batches, but they are assigned the identity PRIOR to the merge into a final sort.
Hopefully with Yukon's Rank() function, these problems may disappear.
-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]

 

[MSGCR]

Thanks for the ideas, all. Using a cursor was the easiest way for me to visualize this. I've heard bad news about using cursors, but this is just a simple fetch and read with not too many records. I don't think system performance will take a hit.

I ended up using a simple identity function to number the locations. Then, The buildings were numbered inside the cursor. If location Id = Location Id then the count would increment, else the counter would reset to 1. i can paste my stored proc if anyone gives a damn. thanks again.

 

[donutman]

NOT IF IT USES A CURSOR!
[flush]
-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]

 

[MSGCR]

Why the hostility towards cursors and User defined functions? I use several functions as they are much cleaner and easier to debug.

 

[donutman]

thread183-956596 has a comparison of a cursor approach to a set-based approach. Vongrunt where's the FAQ. It's getting harder to find that thread.
-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]

Cursors may work, and may even be relatively fast in some situations. But there are very few cases where a cursor is required.

Because the set-based method of data manipulation is so different from the sequential processing of the past (present in cursors), I find in general that people who use cursors really don't understand what set-based is all about. I recommend against cursors for even just this alone: telling people to avoid cursors forces them to learn the set-based method. If you don't know how to do something except with a cursor, you should take the time to learn. It may not be critical for THIS query, but it WILL be critical for some other query. And if you didn't learn it now on an easy one you understand, you'll be lost when you get to a more difficult one.

Besides, unless you actually use the performance monitor to see how long it takes to execute the cursor, you really don't know what kind of a hit it's putting on your system. And cursor cpu time can grow exponentially with the number of records, which is rare for set-based.