Verify Random Sample code? 1

[AlienHost]

Hello, I wrote some code that is supposed to select a random sample of clients from three views. I needed to allow the user to ask for a certain number of records to be returned. What I did was run the parameters of the selection and union the results of the return into a temp table, while using the NewID() function to set a random number on the set. I sorted by the random number, which, in my mind, would randomize the return. I then set the rowcount to whatever number the user wanted and extracted the top data from the temp table. My logic is this: Top sample from randomized set = random sample. Another developer can't understand why this could be true. Could you tell me what you think on this? Following is the code:

CREATE PROCEDURE dbo.usp_PNAuditByCPT @FilterOption VARCHAR(20), @FilterValue VARCHAR(60), @CPTCode VARCHAR(5), @NoteCount INT
	,@StartDate DATETIME, @EndDate DATETIME AS

--Reports used in:
-- 330--ProgressNotesAudit

--EXECUTE dbo.usp_PNAuditByCP 'By Campus', 'WCH', 'T1016','10','1/01/2008','3/30/2008'

/**Force 12AM on begin date***/
SET @StartDate = dbo.udf_GetDate ('S',@StartDate)

/**Force midnight on End date***/
SET @EndDate = dbo.udf_GetDate ('E',@EndDate)

/**Limit return to selection**/
SET @NoteCount= ISNULL(@NoteCount,0)

SET ROWCOUNT 0

-- General progress notes
SELECT newid() AS tempid
--,'gpn' as testview
,v.DocID
,v.CampusInNote AS Campus
,v.TFSProgramInNote AS Program
,v.TFSClientNumber
,RTRIM(FS.NameCalcInit) AS ClientName
,v.ServiceDt AS ServiceDate
,v.ServiceCd AS ServiceCode
,v.CPTCd AS CPT
,v.ServiceDesc AS Service
,ISNULL(v.FundingSource,'Not Entered In Progress Note') AS FundingSource
,v.Detail
,v.DocCreatedDt AS DateCreated
,v.DocCreatorNm AS CreatedBy
Into #AuditTemp
FROM v_ProgressNotesGeneral AS v
INNER JOIN FD__TFS_FACE_SHEET AS FS ON v.TFSClientNumber = FS.TFSClientNumber
WHERE v.ServiceDt >= @StartDate AND v.ServiceDt <= @EndDate
--AND (CPTCd LIKE rtrim(@cptcode) +'%')
AND LEFT(v.CPTCd,5) = @CPTCode
AND 1 = CASE
	WHEN @FilterOption = 'By Campus' AND v.CampusInNote = @FilterValue THEN 1
	WHEN @FilterOption = 'By Program' AND v.TFSProgramInNote = @FilterValue THEN 1
	WHEN @FilterOption = 'By Client' AND v.TFSClientNumber = @FilterValue THEN 1
	WHEN @FilterOption = 'By Creator' AND v.DocCreatorNm LIKE ('%' + RTRIM(@FilterValue) + '%') THEN 1
	ELSE 0
	END

UNION ALL
-- HS progress notes
SELECT newid() AS tempid
--,'hsn' as testview
,v.DocID
,v.CampusInNote AS Campus
,v.TFSProgramInNote AS Program
,v.TFSClientNumber
,RTRIM(FS.NameCalcInit) AS ClientName
,v.ServiceDt AS ServiceDate
,v.ServiceCd AS ServiceCode
,v.CPTCd AS CPT
,v.ServiceDesc AS Service
,ISNULL(v.FundingSource,'Not Entered In Progress Note') AS FundingSource
,v.Detail
,v.DocCreatedDt AS DateCreated
,v.DocCreatorNm AS CreatedBy
FROM v_ProgressNotesHS v
INNER JOIN FD__TFS_FACE_SHEET FS ON v.TFSClientNumber = FS.TFSClientNumber
WHERE v.ServiceDt >= @StartDate AND v.ServiceDt <= @EndDate
--AND (CPTCd LIKE rtrim(@cptcode) +'%')
AND LEFT(v.CPTCd,5) = @CPTCode
AND 1 = CASE
	WHEN @FilterOption = 'By Campus' AND v.CampusInNote = @FilterValue THEN 1
	WHEN @FilterOption = 'By Program' AND v.TFSProgramInNote = @FilterValue THEN 1
	WHEN @FilterOption = 'By Client' AND v.TFSClientNumber = @FilterValue THEN 1
	WHEN @FilterOption = 'By Creator' AND v.DocCreatorNm LIKE ('%' + RTRIM(@FilterValue) + '%') THEN 1
	ELSE 0
	END

UNION ALL
-- OP progress notes
SELECT newid() AS tempid
--,'opn' as testview
,v.DocID
,v.CampusInNote AS Campus
,v.TFSProgramInNote AS Program
,v.TFSClientNumber
,RTRIM(FS.NameCalcInit) AS ClientName
,v.ServiceDt AS ServiceDate
,v.ServiceCd AS ServiceCode
,v.CPTCd AS CPT
,v.ServiceDesc AS Service
,ISNULL(v.FundingSource,'Not Entered In Progress Note') AS FundingSource
,v.Detail
,v.DocCreatedDt AS DateCreated
,v.DocCreatorNm AS CreatedBy
FROM v_ProgressNotesOP v
INNER JOIN FD__TFS_FACE_SHEET FS ON v.TFSClientNumber = FS.TFSClientNumber
WHERE v.ServiceDt >= @StartDate AND v.ServiceDt <= @EndDate
--AND (CPTCd LIKE rtrim(@cptcode) +'%')
AND LEFT(v.CPTCd,5) = @CPTCode
AND 1 = CASE
	WHEN @FilterOption = 'By Campus' AND v.CampusInNote = @FilterValue THEN 1
	WHEN @FilterOption = 'By Program' AND v.TFSProgramInNote = @FilterValue THEN 1
	WHEN @FilterOption = 'By Client' AND v.TFSClientNumber = @FilterValue THEN 1
	WHEN @FilterOption = 'By Creator' AND v.DocCreatorNm LIKE ('%' + RTRIM(@FilterValue) + '%') THEN 1
	ELSE 0
	END
ORDER BY tempid


SET ROWCOUNT @NoteCount



SELECT 
-- tempid
--, testview
DocID
,Campus
,Program
,TFSClientNumber
,ClientName
,ServiceDate
,ServiceCode
,CPT
,Service
,FundingSource
,Detail
,DateCreated
, CreatedBy
FROM #AuditTemp
ORDER BY DateCreated

SET ROWCOUNT 0

GO

 

[RiverGuy]

You've ordered by DateCreated in your final select statement

 

[AlienHost]

Yes, I had just changed that from the TempID late last night. When the other developer saw it, it had the TempID as the final sort. I musta been bleary. Thanks for waking me up! What do you think about the code with it sorted by TempID?

 

[RiverGuy]

Are you using SQL Server 2005 or 2008? You can simply do something such as

SELECT TOP(@SomeIntVariable) Column1, Column2 FROM SomeTable.

 

[RiverGuy]

I forgot to put ORDER BY NEWID() at the end of the statement. It should be:

SELECT TOP(@SomeIntVariable) Column1, Column2 FROM SomeTable ORDER BY NEWID().

 

[AlienHost]

Oh, don't I wish! We're using SQL 2000. In your mind, would it return random samples?

 

[RiverGuy]

I would think your code would return random records should you change that ORDER BY clause.

 

[AlienHost]

I did it right after you pointed it out. Thank you!