Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Using ROW_NUMBER in a sub-query

Posted on 2014-02-20
4
Medium Priority
?
1,188 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Tech or Treat! - Giveaway

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

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
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

598 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