Solved

Using ROW_NUMBER in a sub-query

Posted on 2014-02-20
4
875 Views
Last Modified: 2014-02-20
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?
0
Comment
Question by:Karl_mark
  • 2
  • 2
4 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39873010
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
 

Author Comment

by:Karl_mark
ID: 39873195
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
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 39873285
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
 

Author Closing Comment

by:Karl_mark
ID: 39873369
That's the one. Thanks Guy. I'm sure I tried something along those lines!
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now