Random with SQL , Use of Dense_Rank / Newid()

[Rudolf]

Hello.
I'm not well aware use of DENSE_RANK, but I discover it could be very usefull to avoid long PHP code...
I would like to Take all my Cards from Discard and send them randomly to the Draw Stack.
sublocation will then be 1 to N, where N is the number of cards found as DISCARD in location.
I would like to set this sublocation randomly... this is why i use NEWID() ....
But something is WRONG in my use of DENSE_RANK...

Code: Select all

UPDATE card SET card_location='DRAW',card_sublocation=DENSE_RANK() OVER (PARTITION BY id Order By NEWID()) WHERE card_location='DISCARD' and card_type='EQP'

I've added 'PARTITION BY id' to have an unique field, but I'm not sure it's the good way, And I don't know why adding something there... because DENSE_RANK and NEWID seems to be enough for my needs...
Anyone is able to explain what's wrong?
thanks.

[Rudolf]

Hum... Cannot be used...
DENSE_RANK() and NEWID() are not recognized in version of Mysql used by the server.

[apollo1001]

Probably too late to change your structure, but could you not have used the 'deck' component? Then simply adding the cards to the draw pile and shuffling would have done the trick..

[Rudolf]

No because it's not so simple, one part is fixed, one part is shuffled ... so I prefer to do it myself