They key to simplicity (and, therefor, future maintainability) for this problem is that you have to know how many of each vehicle type to return BEFORE you try to select them. Using Paul's test data, the following meets the spirit of the problem definition...
SET STATISTICS TIME ON
;
--===== Declare some variables to hold the counts for each vehicle type.
DECLARE @CountC INT,
@CountR INT,
@CountT INT
;
--===== Randomly assign the counts to be returned with the constraint that
-- no count must be less than 3 and the total count must be 25.
SELECT @CountC = ABS(CHECKSUM(NEWID()))%16+3,
@CountR = ABS(CHECKSUM(NEWID()))%(25-@CountC-6)+3,
@CountT = 25-@CountC-@CountR
;
--===== Make the random vehicle selection based on the counts and return everything
-- as a single randomly ordered result set.
WITH
cteC AS (SELECT TOP (@CountC) Vehicle_Type, Vehicle_ID FROM #Source WHERE vehicle_type = 'C' ORDER BY NEWID()),
cteR AS (SELECT TOP (@CountR) Vehicle_Type, Vehicle_ID FROM #Source WHERE vehicle_type = 'R' ORDER BY NEWID()),
cteT AS (SELECT TOP (@CountT) Vehicle_Type, Vehicle_ID FROM #Source WHERE vehicle_type = 'T' ORDER BY NEWID()),
cteALL AS (SELECT Vehicle_Type, Vehicle_ID FROM cteC UNION ALL
SELECT Vehicle_Type, Vehicle_ID FROM cteR UNION ALL
SELECT Vehicle_Type, Vehicle_ID FROM cteT)
SELECT Vehicle_Type, Vehicle_ID
FROM cteAll
ORDER BY NEWID()
;
SET STATISTICS TIME OFF
;
In the formulas...
The number "25" is the maximum number of rows to return for all categories combined.
The number "16" is the number 25 minus 9 (the minimum number of rows (3) to return for each category times the number of categories (3)).
The number "3" is the minimum number of rows to return for each category.
The number "6" is minimum number of rows (3) you have to return for the 2 remaining categores.
--Jeff Moden
Change is inevitable... Change for the better is not.