Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sorry for the confusing title, I wasn't sure what to write there.

I have a table of a few hundred records. I need to assign each record of this table to a much smaller dynamic table of users, and the users should alternate as to what records they get assigned.

For example, if TableA is

Row_Number()    Id
1               1
2               2
3               3
4               4
5               5
6               6
7               7
8               8
9               9
10              10

and TableB is

Row_Number()    Id
1               1
2               2
3               3

I need an end result set that is

UserId          RecordId
1               1
2               2
3               3
1               4
2               5
3               6
1               7
2               8
3               9
1               10

I've managed to do something a bit messily using the mod operator, but I was curious if this same query could be run without the temp table and the variable.

The temp table is used because TableA is actually a User Defined Function that converts a comma-delimited string to a table, and I need the Count of the objects from the UDF.

-- Converts a comma-delimited string into a table
SELECT Num as [UserId], Row_Number() OVER (ORDER BY (SELECT 1)) as [RowNo]
INTO #tmpTest
FROM dbo.StringToNumSet('2,3,1', ',') 

DECLARE @test int
SELECT @test = Count(*) FROM #tmpTest

SELECT *
FROM #tmpTest as T1
INNER JOIN (
    SELECT Top 10 Id, Row_Number() OVER (ORDER BY SomeDateTime) as [RowNo]
    FROM TableA WITH (NOLOCK)
) as T2 ON T1.RowNo = (T2.RowNo % @test) + 1

Its important that the UserIds alternate too. I cannot assign the top 1/3 of the records to User1, second 1/3 of the records to User2, and 3rd 1/3 of the records to User3.

Also, the UserIds need to maintain the order in which they were originally entered in, which is why I have a Row_Number() OVER (ORDER BY (SELECT 1)) in the User's table

Is there a way of joining these tables in a single query so I won't need to use a temp table and variable?

I'm using SQL Server 2005

share|improve this question
add comment

2 Answers 2

up vote 12 down vote accepted

Another way to avoid temp tables would be this:

;WITH tmpTest AS
(
    SELECT  Num as [UserId]
            , Row_Number() OVER (ORDER BY (SELECT 1)) as [RowNo]
            , COUNT(*) OVER() AS Quant
    FROM dbo.StringToNumSet('2,3,1', ',') 
)
SELECT *
FROM tmpTest as T1
INNER JOIN 
    (
        SELECT Top 10 Id
            , Row_Number() OVER (ORDER BY SomeDateTime) as [RowNo]
        FROM TableA WITH (NOLOCK)
    ) as T2 ON T1.RowNo = (T2.RowNo % Quant) + 1;
share|improve this answer
    
Ahhh I didn't realize I could use Count(*) with OVER() to get a record count without actually grouping the records. This allows me to get rid of both the temp table and variable, thank you :) –  Rachel Oct 19 '12 at 17:47
add comment

Yes, you can do this without any temp tables:

with w as (select usr_id, row_number() over (order by usr_id) as usr_ordinal from usr)
select record_id, ( select usr_id
                    from w
                    where usr_ordinal=1+(record_ordinal%(select count(*) from w))
                  ) as usr_id
from ( select record_id, row_number() over (order by record_id) as record_ordinal 
       from record ) as z;

See here for a SQLFiddle demo

share|improve this answer
    
While this does get rid of the temp table and variable, I don't like the fact you need to run a sub query for every record :) –  Rachel Oct 19 '12 at 17:45
3  
@Rachel just because that's the way it's declared doesn't mean that's the way it's executed. –  Aaron Bertrand Oct 19 '12 at 17:49
    
@AaronBertrand That's true, but don't like trusting SQL's compiler to determine a good execution plan for me more than I have to. It's caused problems for me in the past :) –  Rachel Oct 19 '12 at 17:52
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.