CODEWITHSUNDEEP
sql-server-2005
Faruz
Faruz

Reputation: 9959

Select rows in random order and then reverse it

I need to select rows in random order and return a query which holds the rows in both regular order and in reverse order. This is done to simulate a fantasy draft for a basketball game I'm working on.

For example, I need a result set as followed:

team1 1
team2 2
team6 3
team9 4
team9 5
team6 6
team2 7
team1 8

As you can see, the first four teams are random then then following four are in reverse order. Hope I managed to explain the problem, if not - please comment and I'll explain further.

Upvotes: 2

Views: 640

Answers (2)

gbn
gbn

Reputation: 432271

You have to "cache" the results of the random ORDER BY.

In this code, if you refer to the CTE in the UNION it will be evaluated twice and you'll have 2 different orders. A CTE is just a macro

;WITH cList AS
(
SELECT team, ROW_NUMBER() OVER (ORDER BY NEWID()) AS rn
FROM teams
)
SELECT * INTO #tempresults FROM cList WHERE rn <= @rn --or however many

SELECT *, rn FROM #tempresults
UNION ALL
SELECT *, (2 * @rn) - rn FROM #tempresults
ORDER BY rn

Duplicating rows is easy with a dummy cross join (like this) but this requires ordering and rownumbering too over the intermediate results. I don't think it can be done in a single SQL statement

Upvotes: 2

Iraklis
Iraklis

Reputation: 810

you can use a query like this:

select top(10) teamname, NewId() as Random
from teams
order by Random

this will return the top ten random teams from your database. Then you can reverse it with some code.

Upvotes: 1

Related Questions