sql server programming: Hello,

Looking at another message, I was able to come up with the following query
to apply a rank to ciites based on the company
for example:
Company1 Denver 1
Company1 Durango 2
Company1 Gunnison 3
Company2 Chicago 1


Table Structure
----------------
Business varchar(255)
City varchar(50)
CrossTabRank integer (identity seed)


SELECT Business, City,
(SELECT COUNT(*) FROM tblBusinessLocation Tloc2
WHERE Tloc2.Business = Tloc.Business AND
Tloc2.BusinessLocation_ID <= Tloc.BusinessLocation_ID) AS
strCityRank

FROM tblBusinessLocation Tloc
ORDER BY Tloc.Business, Tloc.BusinessLocation_ID


Now I need to update the field "CrossTabRank" with the value "strCityRank"
but have no idea how to proceed. I guess I could insert the records into a
temp table and then do the join, but I was wondering if I could do the
update without using a temp table. Any help would be appreciated...

UPDATE tblBusinessLocation
SET CrossTabRank =
(SELECT
(SELECT COUNT(*) FROM tblBusinessLocation Tloc2
WHERE Tloc2.Business = Tloc.Business AND
Tloc2.BusinessLocation_ID <= Tloc.BusinessLocation_ID) AS
strCityRank

FROM tblBusinessLocation Tloc
ORDER BY Tloc.Business, Tloc.BusinessLocation_ID)

Steven,

Try:

UPDATE tblBusinessLocation
SET CrossTabRank =
(SELECT COUNT(*) FROM tblBusinessLocation Tloc2
WHERE Tloc2.Business = Tloc.Business AND
Tloc2.BusinessLocation_ID <= Tloc.BusinessLocation_ID)

--
- Vishal

correction:


UPDATE tblBusinessLocation
SET CrossTabRank =
(SELECT COUNT(*) FROM tblBusinessLocation Tloc2
WHERE Tloc2.Business = tblBusinessLocation.Business AND
Tloc2.BusinessLocation_ID <= tblBusinessLocation.BusinessLocation_ID)

--
- Vishal

[quoted text, click to view]