Using ROW_NUMBER in a sub-query

I have a question about the use of the ROW_NUMBER() function in a sub-query. I have to confess that I do use the function fairly regularly when manipulating data and I find it extremely useful.
I'm writing some scripts to assign members of staff to a room, and I've reached the final few; each remaining member of staff has a number, and I'd like to apply a number to each of the remaining rooms. There are nine members of staff (numbered 1 to 9) and eleven rooms (which I'd like to number 1 - 11) and I'd simply like to join my temp staff table with the temp free rooms table and assign the room based on the number (there has been some more complex manipulation going on previous to do with assigning rooms close to their usual room, but these last nine are the most difficult so they are effectively getting a random room).
So, I have a temporary table called #FreeRooms which has the following fields:
Room VARCHAR(10)
Zone VARCHAR(1)
CountOfZone INT
Free BIT

I want to set the CountOfZone field to an ascending integer if the Free field is currently Null. So, I tried the following:

UPDATE #FreeRooms
SET CountOfZone = 
	(SELECT ROW_NUMBER() OVER(PARTITION BY Free ORDER BY Room)
		FROM #FreeRooms FR2 WHERE FR2.Free IS NULL AND FR1.Room = FR2.Room)
FROM 
	#FreeRooms FR1
WHERE
	FR1.Free IS NULL

Open in new window


Unfortunately, this sets CountOfZone to 1 for every room. I'm assuming that this is because the sub-query runs each time the record is called from #FreeRooms (FR1). IS there a quick way of doing this, or would my best bet be to SELECT the remaning rooms into a temp table with the numeric assignment and then update #FreeRooms with the new value?
Karl_markAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
sorry, it must be WHERE and not AND at that place.
and I had the "rn" 2 times ...
UPDATE #FreeRooms
  SET CountOfZone = 
	( select rn 
           from ( SELECT Room, ROW_NUMBER() OVER(PARTITION BY Free ORDER BY Room) rn
   		    FROM #FreeRooms FR2 
                   WHERE FR2.Free IS NULL 
                ) sq
           where FR1.Room = sq.Room
         )
FROM #FreeRooms FR1
WHERE FR1.Free IS NULL       

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the issue is that the subquery is having the WHERE FR1.room = FR2.roomcondition, which makes the row_number restarts on each room ...

I would think you need a level more for the subquery:
UPDATE #FreeRooms
SET CountOfZone = 
	( select rn 
              from ( SELECT Room, ROW_NUMBER() rn OVER(PARTITION BY Free ORDER BY Room) rn
		FROM #FreeRooms FR2 
             WHERE FR2.Free IS NULL 
              ) sq
     AND FR1.Room = sq.Room
)
FROM 
	#FreeRooms FR1
WHERE
	FR1.Free IS NULL
                                  

Open in new window

0
 
Karl_markAuthor Commented:
Thanks Guy. I've having trouble getting the code to work. I understand the concept, but am having trouble getting the syntax to work. The code above doesn't work if pasted directly; I've tried a couple of variations but am struggling to crack it!
I understand that the sq subquery is effectively acting as a table. The first problem seems to be with the AND clause immediately after the first subquery. I've tried altering this slightly, but so far to no avail! I'll keep trying!
0
 
Karl_markAuthor Commented:
That's the one. Thanks Guy. I'm sure I tried something along those lines!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.