Solved

Using ROW_NUMBER in a sub-query

Posted on 2014-02-20
4
915 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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

825 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