Round robin select - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Round robin select

Author

Topic

Masum7
Starting Member

33 Posts

Posted - 2009-06-04 : 00:14:27

I have a table resources like this:
id userid
1 1
2 1
3 1
4 2
5 2
6 3
7 3
8 3

Here id is pkey.
I want to a select query which will select from resources in round robin fashion based on userid.

I mean something like this:

1 1
4 2
6 3
2 1
5 2
7 3
3 1
8 3

Please note selection of userid 1, 2, 3 then again 1, 2, 3, then 2 is finishes so selection is 1, 3

Masum

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-04 : 00:27:43

SELECT ID, UserID
FROM (
SELECT ID, UserID, ROW_NUMBER() OVER (PARTITION BY UserID ORDER BY ID) AS recID FROM Table1
) AS d
ORDER BY recID, ID

E 12°55'05.63"
N 56°04'39.26"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-04 : 00:27:55

[code]
DECLARE @resource TABLE
(
id int,
userid int
)
INSERT INTO @resource
SELECT 1, 1 UNION ALL
SELECT 2, 1 UNION ALL
SELECT 3, 1 UNION ALL
SELECT 4, 2 UNION ALL
SELECT 5, 2 UNION ALL
SELECT 6, 3 UNION ALL
SELECT 7, 3 UNION ALL
SELECT 8, 3

SELECT id, userid
FROM
(
SELECT *, row_no = row_number() OVER (PARTITION BY userid ORDER BY userid, id)
FROM @resource
) r
ORDER BY row_no, userid

/*
id userid
----------- -----------
1 1
4 2
6 3
2 1
5 2
7 3
3 1
8 3

(8 row(s) affected)
*/
[/code]

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-04 : 00:29:03

took 32 secs to do the formatting

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-04 : 00:31:00

Don't forget you included the sample data too!

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-04 : 00:32:48

I have a question for you khtan. I've often seen you inlude the partition key into the order by key. Is this by habit or is it faster (because of the partition key is the same)?

E 12°55'05.63"
N 56°04'39.26"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-04 : 00:41:32

Never check the performance difference

.

It is more of a habit or thinking when need to sort the rows / results in the usual ORDER BY, brought over to the over() window function.

edit: Just did a quick test, the execution plan are the same

KH
[spoiler]Time is always against us[/spoiler]

Masum7
Starting Member

33 Posts

Posted - 2009-06-04 : 00:54:51

Many thanks khtan & Peso.
Really great. I have also learned about partition.

Thank you guys.

Masum

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-04 : 00:55:24

you are welcome

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-04 : 02:51:36

It seems my suggestion is about 3-5 percent faster. Hardly measurable that is.
And if you think about it, it seems logical that order by two columns is slower than ordering by just one column.
There is no need to order by the partitioning key since that value will the same for whole partition related to the order by statement.

CREATE TABLE	#Robin
		(
			ID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
			UserID INT NOT NULL
		)

INSERT		#Robin
		(
			UserID
		)
SELECT		ABS(CHECKSUM(NEWID())) % 10
FROM		master..spt_values AS v1
INNER JOIN	master..spt_values AS v2 ON v2.Type = 'P'
WHERE		v1.Type = 'P'	

DELETE	f
FROM	(
		SELECT		TOP 50 PERCENT
				ID
		FROM		#Robin
		ORDER BY	NEWID()
	) AS f

CREATE NONCLUSTERED INDEX IX_Robin ON #Robin (UserID, ID)

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
GO

-- Peso
SELECT		ID,
		UserID
FROM		(
			SELECT	ID,
				UserID,
				ROW_NUMBER() OVER (PARTITION BY UserID ORDER BY ID) AS recID
			FROM	#Robin
		) AS r
ORDER BY	recID,
		UserID
GO 2

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
GO

-- Khtan
SELECT		ID,
		UserID
FROM		(
			SELECT	ID,
				UserID,
				ROW_NUMBER() OVER (PARTITION BY UserID ORDER BY UserID, ID) AS row_no
			FROM	#Robin
		) AS r
ORDER BY	row_no,
		UserID
GO 2

DROP TABLE	#Robin

E 12°55'05.63"
N 56°04'39.26"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-04 : 03:34:03

Yes. you are right. Yours is slightly faster.

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources