Hi Everyone!
Assume (highly anonymized):
Create Table myTable
(
ID INT PK,
INDEXNUMBER INT,
VERSION INT,
Data VARCHAR(MAX)
)
This table is used to store mutually exclusive data. For example:
100 1 1 BOB
217 1 2 JOHN
319 1 3 GEORGE
420 7 1 MARY
415 7 2 SUSAN
In this case, I need to randomly pick ONE of BOB, JOHN or GEORGE and ONE of MARY or SUSAN.
I'm happy with either the ID or the INDEXNUMBER/VERSION pair.
If it helps to think about it, it's like picking a single shift of a hockey team from a table containing a roster:
Pick 1 Center from 3 available, Pick 1 Left Wing from 5 available, etc.
I've been playing with NEWID() and MAX/MIN (Cast NEWID to varchar first) but I keep getting hung up on the GROUP BY. If I GROUP BY ID, then max is operating on a
single row at a time, yielding the entire table.
If I GROUP BY INDEXNUMBER, VERSION I get a similar result (The pair being unique).
What I need to do is GROUP BY INDEXNUMBER (excluding ID from the query entirely) yet somehow retrieve the VERSION.
Thanks in advance!
AVADHESH PATEL
07-May-2013