Return groups in random order

Question

Post reply

Return groups in random order

Andy Lucas

SSC Veteran

Points: 295
More actions
April 8, 2010 at 5:49 am

#213617

The need is to return groups in a random way.
FranchiseID's own one or more unique SectorID's
There is a result list containing, amongst other columns, FranchiseID and SectorID
this needs to be gouped into FranchiseID with each group ordered by SectorID, but then each FranchiseID group needs to appear in the result list in a different order (randomly) everytime the query is run.
Many thanks for your help.
Andy

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 1

Andy, how about you going through this following article and helping us help you?? 🙂

FORUM POSTING ETIQUETTES - JEFF MODEN

When u do so, i am sure a lot of us will help u instantly...So please post

1. Table Structures - CREATE TABLE scripts

2. Constraints or Indexes in the table, - CREATE SCRIPTS

3. SAMPLE DATA - INSERT INTO TABLE scripts

4. Desired output - some visual representation of this.

I could guess very few information from your post mate 🙁

Cheers!!

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 2

I second ColdCoffee's motion. Sample schema with scripts to populate them with sample data and a picture of expected results are what we need to give definitive (even coded and tested) answers. Verbal descriptions, even if they are long and detailed, just don't tell us enough. But I will share one quick tip:

ORDER BY NEWID()

This produces a random order. Hopefully it is enough to get you going. Let us know.

Good luck.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

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

DECLARE @Example

TABLE (

franchise_id INTEGER NOT NULL,

sector_id INTEGER NOT NULL,

data CHAR(1) NOT NULL

);

INSERT @Example

(franchise_id, sector_id, data)

SELECT 1, 1, 'A' UNION ALL

SELECT 1, 2, 'C' UNION ALL

SELECT 1, 3, 'E' UNION ALL

SELECT 2, 6, 'G' UNION ALL

SELECT 2, 5, 'I' UNION ALL

SELECT 2, 4, 'L' UNION ALL

SELECT 3, 7, 'N' UNION ALL

SELECT 3, 8, 'P' UNION ALL

SELECT 4, 6, 'S' UNION ALL

SELECT 4, 5, 'V';

SELECT E.franchise_id,

E.sector_id,

E.data

FROM @Example E

JOIN (

SELECT franchise_id,

random = MAX(CONVERT(CHAR(37),NEWID()))

FROM @Example E

GROUP BY

franchise_id

) E2

ON E2.franchise_id = E.franchise_id

ORDER BY

E2.random, E.sector_id;

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 4

Looks like too much of code to me too. But this works

IF OBJECT_ID( 'tempdb..#tmpOrdered' ) IS NOT NULL

DROP TABLE #tmpOrdered

DECLARE@tblTable TABLE

(

FranchiseID INT,

SectorID INT

)

INSERT@tblTable

SELECT 1, 1 UNION ALL

SELECT 1, 2 UNION ALL

SELECT 2, 1 UNION ALL

SELECT 2, 2 UNION ALL

SELECT 2, 3 UNION ALL

SELECT 3, 1

SELECTFranchiseID, NEWID() [NewID]

INTO#tmpOrdered

FROM(

SELECT DISTINCT FranchiseID

FROM@tblTable

) N

; WITH cte_TableName AS

(

SELECTROW_NUMBER() OVER ( PARTITION BY T.FranchiseID ORDER BY T.SectorID ) RowNum, T.FranchiseID,

T.SectorID, N.[NewID]

FROM@tblTable T

INNER JOIN #tmpOrdered N ON T.FranchiseID = N.FranchiseID

)

SELECTFranchiseID, SectorID

FROMcte_TableName

ORDER BY [NewID]

IF OBJECT_ID( 'tempdb..#tmpOrdered' ) IS NOT NULL

DROP TABLE #tmpOrdered

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Andy Lucas SSC Veteran Points: 295 More actions · Answer 5

Well thank you for your prompt replies.

I was working on working out how to do as Jeff Moden says

Then I looked up and bingo the answer is there, twice!

I have not the capacity to explain WHY both the examples work but work they do.

Can you point out the differences in methods for me please bearing in mind my limited capabilities?

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 6

My method makes use of ROW_NUMBER() and NEWID(), ROW_NUMBER() a ranking function and NEWID() a function that returns a unique value for every row in the result. You can Google for more information on these two functions.

Paul's example would be simpler for you to understand as it doesnot use ROW_NUMBER(). I would suggest you to try and understand Paul's example. Hope this helps..:-)

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Andy Lucas SSC Veteran Points: 295 More actions · Answer 7

Thank you all very much.

I have implemented Pauls answer into my live code and it is working great!

Cheers

Andy

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

Cool - they are essentially the same method - assign a random value to each distinct group, and sort by that first. The only bit of trickery in my example is the CHAR(37) conversion...which is just because you can't perform an aggregate like MAX directly on the GUID returned by NEWID.

If anything else isn't clear, please ask - I'm happy to explain 😎

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi