Improve company productivity with a Business Account.Sign Up

x
?
Solved

Using ROW_NUMBER in a sub-query

Posted on 2014-02-20
4
Medium Priority
?
1,453 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 143

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 143

Accepted Solution

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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.

Join & Write a Comment

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Here is how to restore SQL Server database to the point in time.  Follow the step by step approach to restore your database at a specific point in time and also understand its alternate approach.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

579 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