select random records but for each category

Question

select random records but for each category

Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

mister.magoo SSC-Forever Points: 47068 More actions · Answer 1

Paul White (3/15/2010)
mister.magoo (3/15/2010)
I think the results are to my mind "more random"
:blink: Really? 😉
There are some good points in your post, but I have to disagree with that one.

I did say "in my mind" - that can be a very odd place 😉

Your code is compact (though arguably who cares once it is in a function?) but slower, as you say.
I will give some more thought to this later.
I don't want to come across as just being critical - I enjoyed your post. It is an interesting challenge isn't it?
Side note: I had an email from the OP last night clarifying the real requirements. He should be posting them here soon, just to add an interesting twist.
Paul

And I don't mean to be critical either, I am completely amazed that yours is so quick.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

mister.magoo SSC-Forever Points: 47068 More actions · Answer 2

@paul-2 and @jeff

I have re-read my first post and can see that it might come across in the wrong way, sorry for that.

I think the work you both did on this thread is excellent and as always I have taken a little something away from it for future use.

The code Paul posted is lightning fast but when I saw it I could not believe that the problem required all that code, so attempted to do it myself - for my benefit.

When I found a solution that was (to my mind at least) simpler, I felt it would be a benefit to the site if I posted it to show an alternative way of achieving the requirement.

Along the way, I compared it to Paul's (as a reference point to make sure mine was working correctly) and noticed that Paul's solution would fail if there happened to be a shortfall of records.

I thought it best to point that out, so that you could take a look and tweak your code.

I hope you don't take that the wrong way - it was meant to be helpful - albeit in a late night / tired / rubbish way 😀

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Paul White SSC Guru Points: 150467 More actions · Answer 3

mister.magoo,

There really was no need for that, for my part, but thanks for your kind words anyway 🙂

You do raise an interesting point regarding the potential for incorrect results if fewer than three records exist.

The idea of balancing the returned set to reflect the distribution of the data across the groups is also good.

I hope to get back to this shortly.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1003867 More actions · Answer 4

mister.magoo (3/16/2010)
I hope you don't take that the wrong way - it was meant to be helpful - albeit in a late night / tired / rubbish way 😀

Heh... we've all been there. Not to worry and thank you for the concern and the nice feedback.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)