Selecting Random Records
May 11, 2008hi again,
i just want to ask if how can i randomly select 5 distinct records from a table w/ a hundreds of records everytime i exec a stored procedure??
thanks
ADVERTISEMENT
Selecting 10 Random Records
Mar 1, 2004How would I create a statement that would select 10 random records from the SQL DB?
View 5 Replies View RelatedSelecting Records By Random Order
Sep 22, 2000Hello,
I need to select records by random order everytime user go to that page. QuestionID is the uniq field in this table.
SELECT * FROM QuestionMaster.
I tried to use the following statement to get the random records but I can't get the value in the variable out of this statement that I can use in my above select statement. Please HELP.
SELECT Convert(int,RAND()*97)
Thanks in a million.
Sarika
Returning Random Records And NOT Similar (random Questions)
Jul 20, 2005Hi,I need to extract randomly 5 records from the table "Questions". Now I useSELECT TOP 5 FROM Questions ORDERBY NEWID()And it works. The problem is that I need an additional thing: if SQLextracts record with ID=4, then it should not extract record with ID=9,because they are similar. I mean, I'd like something to tell SQL that if itextracts some questions, then it SHOULD NOT extract other ones.How can I do it?Thanks!Luke
View 1 Replies View RelatedSelecting A Random Row
Mar 4, 2008Im trying to write a statement that only selects one of the possible rows that could be given.
For example:
Car ID: Colours Available:
1 Blue
1 Green
1 Black
I want the statement to return:
Car ID: Colours Available:
1 Blue(Random Selection)
Is this possible?
Thanks
Selecting Random Data From Sql Server
Jan 21, 2008I have three tables that are joined together to retrieve a piece of data.Table One has the columns pid1(PK), idTable Two has the columns pid1, qid1, idTable three has columns qid1, question, answerHere is my sql statement:SELECT Table1.pid1, Table2.qid1, Table3.Question FROM Table1 JOIN Table2 ON Table1.pid1=Table2.pid1 JOIN Table3 ON Table2.qid1=Table3.qid1 WHERE Table1.pid=...(N)I put an elipsis because I'm stuck on how to proceed from here. See I need to randomly select a value from column pid1 in table1 then use this value to replace N in the sql statement, then have the statement run and randomly return exactly one piece of data instead.Is this possible? How can you randomly get one piece of data from joining three table without a WHERE CLAUSE.
View 1 Replies View RelatedSELECTing Random Record From TABLE?
Apr 24, 2001Hello.
I need to select a random record from TABLE. It might look easy with using RAND() function, but the tricky part is that ID's which are the PRIMARY KEY, were assigned as a random number.
So right now ID's in that TABLE look some thing like that: -18745, 45809, 129, -5890023, 487910943, -209, etc...
If any one have any ideas please respond.
Thanks in advance.
Random Records
Nov 16, 1999Hi,
I'm trying to use the following query to select two random records from my database. Do you have any ideas of why the recrand field will not change? I am using MS Sql server 7.. Please email mark@dtdesign.com ..Cheers Mark
SELECT TOP 2 mytable.id AS RECID, RAND(mytable.id) AS recrand
FROM mytable
ORDER BY recrand
Get Random Records..how ? Please
Feb 10, 2007Hi every one.How can I get random 4 records in my table.
Example I want to get top 4 random records
Select top 4 from tbl_Products.....?
Thanks in appreciate
Random Records Selection
Mar 13, 2004hi im creating 2 random numbers, matching one of them (i) to the QuestionsNo column in my database, and then making it display the whole record. the code below generate 2 random numbers but i cannot get the rest of it to work. i need it to match the random number to the QuestionNo field in my database and display the whole record. can anyone help me and tell me where im going wrong please????
Dim i,j as integer
Randomize
i = CInt(Int((10 * Rnd()) + 1))
j = CInt(Int((10 * Rnd()) + 1))
example.InnerHtml = "Random Number : " & i
example1.InnerHtml = "Random Number : " & j
strSQL = "SELECT * FROM Table1 WHERE QuestionNo = " & i &""
Message.text=strSQL.ToString()
Random Records Script
Jun 5, 2001Hello,
I downloded the random record script from swynk site and when I run It gives me error msg
Server: Msg 170, Level 15, State 1, Procedure PROC_RANDOM, Line 73
Line 73: Incorrect syntax near 'DECLARE CUR_MEU CURSOR SCROLL FOR SELECT TOP '.
Server: Msg 170, Level 15, State 1, Procedure PROC_RANDOM, Line 101
Line 101: Incorrect syntax near 'DECLARE CUR_MEU CURSOR SCROLL FOR SELECT TOP '.
Can anyone guide me
I've copied the script
Thanks.
CREATE PROCEDURE PROC_RANDOM @VEZES INT , @TABELA CHAR(50) , @CAMPO CHAR(50) , @REPETIR CHAR(1) = "S"
AS
-- DECLARATION OF VARIABLES
DECLARE @CONTADOR INT , @VAL INT , @AUX CHAR(10)
DECLARE @TOTAL INT , @VALOR INT
DECLARE @NAME CHAR(50)
-- NO MESSAGES
SET NOCOUNT ON
-- CHECK FOR THE TABLE CALLED 'RETORNO'
IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE ID = OBJECT_ID(N'[DBO].[RETORNO]') AND OBJECTPROPERTY(ID, N'ISUSERTABLE') = 1)
DROP TABLE [DBO].[RETORNO]
-- CREATES THE TABLE
CREATE TABLE RETORNO
(
CAMPO CHAR(50)
)
-- IF THE USER DON'T WANT SAME RESULTS , CREATE A TEMPORARY TABLE
IF @REPETIR = 'N'
BEGIN
CREATE TABLE #ALE
(
COD INT
)
END
-- GET THE TOTAL NUMBER OF RECORDS , USING THE SYSINDEXES TABLE
SELECT @TOTAL = ROWS FROM SYSINDEXES
WHERE ID = object_ID(@TABELA)
AND INDID < 2
-- IF THERE IS A TABLE....
IF @TOTAL IS NOT NULL
BEGIN
-- CLEAN THE COUNTER
SELECT @CONTADOR = 0
-- DO A LOOP SEVERAL TIMES TO GET THE NUMBER , AND RECORDS , OF RANDOM RECORDS THAT THE USER WANT
WHILE @CONTADOR <> @VEZES
BEGIN
-- INCREASE THE COUNTER
SELECT @CONTADOR = @CONTADOR + 1
-- GET A RANDOM NUMBER USING THE DATE....
SELECT @VAL = REVERSE((DATEPART(mm, GETDATE()) * 100000 )
+ (DATEPART(ss, GETDATE()) * 1000 )
+ DATEPART(ms, GETDATE()))
SELECT @AUX = CONVERT(CHAR,@VAL * ( @CONTADOR ) )
SELECT @AUX = REVERSE(SUBSTRING(@AUX,3,5))
SELECT @VALOR = (CONVERT(INT,@AUX)%@TOTAL)+1
-- IF THE USER DON'T WANT TO REPEAT DATA...
IF @REPETIR = 'N'
BEGIN
-- CHECK FOR THE USE OF THIS NUMBER
IF NOT EXISTS(SELECT COD FROM #ALE WHERE COD = @VALOR)
BEGIN
-- CREATE A DYNAMIC CURSOR WITH SOME RECORDS. THE LAST WILL BE THAT RANDOM
EXECUTE("DECLARE CUR_MEU CURSOR SCROLL FOR SELECT TOP " + @VALOR + " " + @CAMPO+ " FROM " +@TABELA )
OPEN CUR_MEU
-- GO TO THE LAST RECORD
FETCH LAST FROM CUR_MEU INTO @NAME
-- INSERT THE DATA IN THE RETURN TABLE
INSERT INTO RETORNO VALUES(@NAME)
CLOSE CUR_MEU
DEALLOCATE CUR_MEU
-- STORE THE RANDOM NUMBER IN A TABLE TO AVOID THE USE OF THIS NUMBER AGAIN
INSERT INTO #ALE VALUES(@VALOR)
END
ELSE
BEGIN
-- IF THIS NUMBER WAS USED THEN DECREASE THE COUNTER AND TRY AGAIN...
SELECT @CONTADOR = @CONTADOR - 1
END
END
ELSE
BEGIN
-- CREATE A DYNAMIC CURSOR WITH SOME RECORDS. THE LAST WILL BE THAT RANDOM
EXECUTE("DECLARE CUR_MEU CURSOR SCROLL FOR SELECT TOP " + @VALOR + " " + @CAMPO+ " FROM " +@TABELA )
OPEN CUR_MEU
-- GO TO THE LAST RECORD
FETCH LAST FROM CUR_MEU INTO @NAME
-- INSERT THE DATA IN THE RETURN TABLE
INSERT INTO RETORNO VALUES(@NAME)
CLOSE CUR_MEU
DEALLOCATE CUR_MEU
END
END
SET NOCOUNT OFF
END
-- IF THE TABLE WAS CREATED , WE MUST DROP IT
IF @REPETIR = 'N'
BEGIN
DROP TABLE #ALE
END
-- RETURN TO THE USER ALL RANDOM RECORDS
SELECT CAMPO FROM RETORNO
-- DROP THE TABLE WITH THE RANDOM RECORDS
DROP TABLE RETORNO
GO
/* END OF THE STORED PROCEDURE */
Pick Random Records
Jun 25, 2002I need a way to select records at random from a SQL Server 2000 table. If the record I've selected at random meets a few simple conditions (column X <> 'A' and column Y == 'Z') then return this record and get the next. Ideally I'd be able to do this in a stored proc where I'd call it with:
parameter 1: table name
parameter 2: number of random records to get
parameter 3: value for condition 1
parameter 4: value for condition 2
Any help will be greatly appreciated!
Patrick
Random Records Script
Jun 5, 2001Hello,
I downloded the random record script from swynk site and when I run It gives me error msg
Server: Msg 170, Level 15, State 1, Procedure PROC_RANDOM, Line 73
Line 73: Incorrect syntax near 'DECLARE CUR_MEU CURSOR SCROLL FOR SELECT TOP '.
Server: Msg 170, Level 15, State 1, Procedure PROC_RANDOM, Line 101
Line 101: Incorrect syntax near 'DECLARE CUR_MEU CURSOR SCROLL FOR SELECT TOP '.
Can anyone guide me
I've copied the script
Thanks.
CREATE PROCEDURE PROC_RANDOM @VEZES INT , @TABELA CHAR(50) , @CAMPO CHAR(50) , @REPETIR CHAR(1) = "S"
AS
-- DECLARATION OF VARIABLES
DECLARE @CONTADOR INT , @VAL INT , @AUX CHAR(10)
DECLARE @TOTAL INT , @VALOR INT
DECLARE @NAME CHAR(50)
-- NO MESSAGES
SET NOCOUNT ON
-- CHECK FOR THE TABLE CALLED 'RETORNO'
IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE ID = OBJECT_ID(N'[DBO].[RETORNO]') AND OBJECTPROPERTY(ID, N'ISUSERTABLE') = 1)
DROP TABLE [DBO].[RETORNO]
-- CREATES THE TABLE
CREATE TABLE RETORNO
(
CAMPO CHAR(50)
)
-- IF THE USER DON'T WANT SAME RESULTS , CREATE A TEMPORARY TABLE
IF @REPETIR = 'N'
BEGIN
CREATE TABLE #ALE
(
COD INT
)
END
-- GET THE TOTAL NUMBER OF RECORDS , USING THE SYSINDEXES TABLE
SELECT @TOTAL = ROWS FROM SYSINDEXES
WHERE ID = object_ID(@TABELA)
AND INDID < 2
-- IF THERE IS A TABLE....
IF @TOTAL IS NOT NULL
BEGIN
-- CLEAN THE COUNTER
SELECT @CONTADOR = 0
-- DO A LOOP SEVERAL TIMES TO GET THE NUMBER , AND RECORDS , OF RANDOM RECORDS THAT THE USER WANT
WHILE @CONTADOR <> @VEZES
BEGIN
-- INCREASE THE COUNTER
SELECT @CONTADOR = @CONTADOR + 1
-- GET A RANDOM NUMBER USING THE DATE....
SELECT @VAL = REVERSE((DATEPART(mm, GETDATE()) * 100000 )
+ (DATEPART(ss, GETDATE()) * 1000 )
+ DATEPART(ms, GETDATE()))
SELECT @AUX = CONVERT(CHAR,@VAL * ( @CONTADOR ) )
SELECT @AUX = REVERSE(SUBSTRING(@AUX,3,5))
SELECT @VALOR = (CONVERT(INT,@AUX)%@TOTAL)+1
-- IF THE USER DON'T WANT TO REPEAT DATA...
IF @REPETIR = 'N'
BEGIN
-- CHECK FOR THE USE OF THIS NUMBER
IF NOT EXISTS(SELECT COD FROM #ALE WHERE COD = @VALOR)
BEGIN
-- CREATE A DYNAMIC CURSOR WITH SOME RECORDS. THE LAST WILL BE THAT RANDOM
EXECUTE("DECLARE CUR_MEU CURSOR SCROLL FOR SELECT TOP " + @VALOR + " " + @CAMPO+ " FROM " +@TABELA )
OPEN CUR_MEU
-- GO TO THE LAST RECORD
FETCH LAST FROM CUR_MEU INTO @NAME
-- INSERT THE DATA IN THE RETURN TABLE
INSERT INTO RETORNO VALUES(@NAME)
CLOSE CUR_MEU
DEALLOCATE CUR_MEU
-- STORE THE RANDOM NUMBER IN A TABLE TO AVOID THE USE OF THIS NUMBER AGAIN
INSERT INTO #ALE VALUES(@VALOR)
END
ELSE
BEGIN
-- IF THIS NUMBER WAS USED THEN DECREASE THE COUNTER AND TRY AGAIN...
SELECT @CONTADOR = @CONTADOR - 1
END
END
ELSE
BEGIN
-- CREATE A DYNAMIC CURSOR WITH SOME RECORDS. THE LAST WILL BE THAT RANDOM
EXECUTE("DECLARE CUR_MEU CURSOR SCROLL FOR SELECT TOP " + @VALOR + " " + @CAMPO+ " FROM " +@TABELA )
OPEN CUR_MEU
-- GO TO THE LAST RECORD
FETCH LAST FROM CUR_MEU INTO @NAME
-- INSERT THE DATA IN THE RETURN TABLE
INSERT INTO RETORNO VALUES(@NAME)
CLOSE CUR_MEU
DEALLOCATE CUR_MEU
END
END
SET NOCOUNT OFF
END
-- IF THE TABLE WAS CREATED , WE MUST DROP IT
IF @REPETIR = 'N'
BEGIN
DROP TABLE #ALE
END
-- RETURN TO THE USER ALL RANDOM RECORDS
SELECT CAMPO FROM RETORNO
-- DROP THE TABLE WITH THE RANDOM RECORDS
DROP TABLE RETORNO
GO
/* END OF THE STORED PROCEDURE */
Insert Random Records..HELP!
Jul 1, 2004Once again - My table should consist of 100 new records for a field MobilePhone(of char type) and last 5 digits should be randomly choosed (should be like this: +381randomno1randomno2.. etc.(example: +38156465, where '+' sign makes it char type and digits after +381 are randomly choosed. :confused: Anyone knows how to solve this....PLEASE?
View 3 Replies View RelatedSelect 10 Random Records
Apr 22, 2008Hello,
Is it possible to select 10 random records from a table?
Thanks,
Miguel
10 Random Records, 1 From Each Category
Dec 1, 2006I'm not sure if this is a completely dumb question, but please humor me:) I have a table of records, called Records, each of which has aCategory_ID that places it in a specific category; the details of thecategories are stored in another table called Category. What I need todo is retrieve a recordset that contains one record from each category,but where the records that are retrieved are random. I know how toretrieve one or more random records using "order by NewID()", but havenot been able to work out how to get one random record from eachcategory.Any assistance in this puzzler will be HUGELY appreciated!ThanksFEB
View 7 Replies View RelatedReturning 30% Random Records
Sep 27, 2007HI,
If i have the following data
Code Block
Create Table #Request (
[requestid] int ,
[customername] Varchar(30) ,
[age] int ,
[sex] char(1) ,
[address] Varchar(30) ,
[status] int
);
Insert Into #request Values('2342','Jack','23','M','Texas','0');
Insert Into #request Values('223452','Tom','45','M','Ohio','1');
Insert Into #request Values('22353','Bobby','23','M','Austin','0');
Insert Into #request Values('22362','Guck','23','M','Austin','0');
Insert Into #request Values('22392','Luck','23','M','Austin','1');
Insert Into #request Values('22362','Buck','23','M','Austin','0');
Insert Into #request Values('2564392','Jim','23','M','Austin','1');
Insert Into #request Values('2342','Jasm','23','M','Austin','0');
Insert Into #request Values('2765492','Chuck','23','M','Austin','1');
How can i return 30% random requestid's from this table?
thanks.
Cant Work Out This SQL To Return 4 Random Records
Feb 10, 2007I've got a product table in SQL 2000 that contains say these rows:
ProductID intProductName varcharIsSpecial bit
I want to always return 4 random specials from a query and can do this fine by using:
SELECT TOP 4 ProductID,ProductNameFROM Products WHERE IsSpecial = 1ORDER BY NEWID()
This works ok if there are 4 products marked as specials but the problem is i always need 4 records returned but if only 2 products are marked as special then only 2 records are returned.
What i really need is something in there that says if <4 records are returned then just add random non-special products to make the total products returned up to 4. So it should always be 4 records but preference is given to specials if you see what i mean?
Is this possible?
Thanks
Select Random Records From MS SQL Table?
Aug 26, 2005Hello,I have 1000 of records in my table. I wanna select random 100 of them? How?regards
View 3 Replies View RelatedQuery Random Records With Sum Creteria
Sep 7, 2005Dear All ...
im new memmber in this great forum ..
i need help ...
i have Table like :
ID Subject Points
---- ------- --------
1 1 10
2 1 20
3 2 10
4 3 5
5 1 15
6 2 10
7 3 10
8 1 20
i need to select random records from table where Subject = 1 and Total Sum for selected records Points = 25
any one can help me ..
thanks
Random Loop Through Table Without Using The Same Records Over Again.
Jul 31, 2007Hello,
Anyone have any suggestions on creating a query that will randomly select records from a table, but not use those records again. I have some code that does it, but it uses the same fields over again, and also throws in some blank records that I did not specify in the query. I am creating a test engine that has to randomly ask questions.
How To Assign Random Number To One Column For 500 Records?
Aug 4, 2005I try to set up a testing sample table which contain one integer project_ID field for table Sample around 500 records, and want the project_ID to be random number within 1 to 99, how to implement script todo it?
thanks!
How To Make 2 Columns In Datagrid With Random Records?
Aug 20, 2005Hi
I want to make random record from both columns
Not just like this
"SELECT Firstname,Lasename FROM rndnames ORDER BY NewID()"
but more like this but this code dont work becouse i dont know how to put it into the code:
"SELECT Firstname FROM rndnames ORDER BY NewID()"
"SELECT Lasename FROM rndnames ORDER BY NewID()"
As you see i want both columns to be random placed..
Please help me...
How To Select Random Records From Database (advanced)
Mar 12, 2006hello i have a problem and i dont know if it can fixed or not i'm using asp.net 2 and sqlserver 2005 i have a table named questions in the database and that table has 5 columns questionid,question,answer1,answer2,answer3 every questions has 3 answers 1 right and the other 2 are wrong what i want to do is select 10 random questions and their right and wrong answers show in my asp.net page as application the answers will be in radio buttons so is that can be done or it's not possible.thanks all
View 17 Replies View RelatedSQL Union Problems When Trying To Retrieve Random Records
Jun 6, 2006I have a situation where i am trying to run two query's and retrieveone record from each query, then using Union combine them into onerecordset. The First Query is strait forward and should just returnthe matching record however the second query needs to find a randomrecord and return it. The union is causing me some trouble. It seemsthat any method i try to find a random record just causes an error.Here is an example of a query that's causing the problem.--------------------------------------------------------------------------------------------------------declare @variable1 smallintdeclare @variable2 intset @variable1 = 10002set @variable2 = 1001211720select col1, col2, col3, col4, col5, col6from table1where col1 = @variable1 and col2 = @variable2unionselect col1, col2, col3, col4, col5, col6from table2where col2 = @variable2order by newID()--------------------------------------------------------------------------------------------------------I have also tried the following which does not provide an error but icannot find a way only to return one record from the second query--------------------------------------------------------------------------------------------------------declare @variable1 smallintdeclare @variable2 intset @variable1 = 10002set @variable2 = 1001211720select newID() as colID, col1, col2, col3, col4, col5, col6from table1where col1 = @variable1 and col2 = @variable2unionselect newID() as colID, col1, col2, col3, col4, col5, col6from table2where col2 = @variable2order by colIDIf anyone has any ideas, fixes, or thoughts i would appreciate them...thanks in advance...FYI i am fairly inexperienced in SQL so please feel free to let me knowif you need more of an explination.
View 9 Replies View RelatedDynamic SQL And NewID Function - Pulling Random Records
Jun 11, 2007I'm trying to use the NEWID function in dynamic SQL and get an errormessage Incorrect syntax near the keyword 'ORDER'. Looks like I can'tdo an insert with an Order by clause.Here's the code:SELECT @SQLString = N'INSERT INTO TMP_UR_Randoms(Admit_DOCID,Client_ID, SelectDate, SelectType,RecordChosen)'SELECT @SQLString = @SQLString + N'(SELECT TOP ' + @RequFilesST + 'Admit_DOCID, Client_ID, SelectDate, SelectType, RecordChosen FROMFD__UR_Randoms 'SELECT @SQLString = @SQLString + N'WHERE SelectType = ' +@CodeRevTypeSt + ' AND SelectDate = ''' + @TodaySt + '''' + ' ORDERBY NEWID())'execute sp_executesql @SQLStringMy goal is to get a random percentage of records.The full SP follows. In a nutshell - I pull a set of records fromFD__Restart_Prog_Admit into a temporary table called FD__UR_Randoms.I need to retain the set of all records that COULD be eligible forselection. Based on the count of those records, I calculate how manyneed to be pulled - and then need to mark those records as "chosen".I'd just as soon not use the TMP_UR_Randoms table - I went that routebecause I ran into trouble with a #Tmp table in the above SQL.Can anyone help with this? Thanks in advance.Full SQL:CREATE PROCEDURE TP_rURRandomReview @ReviewType varchar(30)--Review type will fill using Crystal Parameter (setting defaults)AS/* 6.06.2007UR Requirements:(1) Initial 4-6 month review: 15% of eligible admissions(eligible via days in program and not yet discharged) must be reviewed4-6 months after admission. This review will be done monthly -meaning we'll have a moving target of names (with overlaps) whichcould be pulled from each month. (Minimum 5 records)(2) Subsequent 6-12 month review: Out of those already reviewed(in #1), we must review 25% of them (minimum of 5 records)(3) Initial 6-12 month review: Exclude any included in 1 or 2 -review 25% of admissions in program from 6-12 months (minimum 5)*/DECLARE @CodeRevType intDECLARE @PriorRec int -- number of records already markedeligible (in case user hits button more than once on same day for sametype of review)DECLARE @CurrRec int --number of eligible admitsDECLARE @RequFiles intDECLARE @SQLString nvarchar(1000)DECLARE @RequFilesSt varchar(100)DECLARE @CodeRevTypeSt char(1)DECLARE @TodayNotime datetimeDECLARE @TodaySt varchar(10)--strip the time off todaySELECT @TodayNotime = DateAdd(day,datediff(day,0,GetDate()),0)--convert the review type to a codeSelect @CodeRevType = Case @ReviewType when 'Initial 4 - 6 Month' then1 when 'Initial 6 - 12 Month' then 2 when 'Subsequent 6 - 12 month'then 3 END--FD__UR_Randoms always gets filled when this is run (unless it waspreviously run)--Check to see if the review was already pulled for this recordSELECT @PriorRec = (Select Count(*) FROM FD__UR_Randoms whereSelectType = @CodeRevType and SelectDate = @TodayNotime)If @PriorRec 0 GOTO ENDThis--************************************STEP A: Populate FD__UR_Randomstable with records that are candidates for review************************If @CodeRevType = 1BEGININSERT INTO FD__UR_Randoms (Admit_DOCID, Client_ID, SelectDate,SelectType,RecordChosen)(SELECT pa.OP__DOCID, pa.Client_ID,Convert(varchar(10),GetDate(),101) as SelectDate, @CodeRevType, 'F'FROM dbo.FD__RESTART_PROG_ADMIT paInner join FD__Client cOn pa.Client_ID = c.Client_IDWHERE Left(c.Fullname,2) <'TT' AND (Date_Discharge IS NULL)AND(DATEDIFF(d, Date_Admission, GETDATE()) 119)AND (DATEDIFF(d, Date_Admission, GETDATE()) <= 211)AND pa.OP__DOCID not in (Select Admit_DOCID from FD__UR_Randomswhere RecordChosen = 'T'))ENDIf @CodeRevType = 2--only want those that were selected in a batch 1 - in program 6-12months; selected for first reviewBEGININSERT INTO FD__UR_Randoms (Admit_DOCID, Client_ID, SelectDate,SelectType,RecordChosen)(SELECT pa.OP__DOCID, pa.Client_ID,Convert(varchar(10),GetDate(),101) as SelectDate, @CodeRevType, 'F'FROM dbo.FD__RESTART_PROG_ADMIT paInner join FD__Client cOn pa.Client_ID = c.Client_IDWHERE Left(c.Fullname,2) <'TT' AND (Date_Discharge IS NULL)AND(DATEDIFF(d, Date_Admission, GETDATE()) 211)AND (DATEDIFF(d, Date_Admission, GETDATE()) < 364)AND pa.OP__DOCID in (Select Admit_DOCID from FD__UR_Randomswhere SelectType = 1 AND RecordChosen= 'T'))ENDIf @CodeRevType = 3--only want those that were not in batch 1 or 2 - in program 6 to 12monthsBEGININSERT INTO FD__UR_Randoms (Admit_DOCID, Client_ID, SelectDate,SelectType,RecordChosen)(SELECT pa.OP__DOCID, pa.Client_ID,Convert(varchar(10),GetDate(),101) as SelectDate, @CodeRevType, 'F'FROM dbo.FD__RESTART_PROG_ADMIT paInner join FD__Client cOn pa.Client_ID = c.Client_IDWHERE Left(c.Fullname,2) <'TT' AND (Date_Discharge IS NULL)AND(DATEDIFF(d, Date_Admission, GETDATE()) 211)AND (DATEDIFF(d, Date_Admission, GETDATE()) < 364)AND pa.OP__DOCID NOT in (Select Admit_DOCID from FD__UR_Randomswhere SelectType < 3 AND RecordChosen= 'T'))ENDSELECT @CurrRec = (Select Count(*) FROM FD__UR_Randoms whereSelectType = @CodeRevType and SelectDate = @TodayNoTime)--*************************************STEP B Pick the necessarypercentage **************************************--if code type = 1, 15% otherwise 25%If @CodeRevType = 1BEGINSELECT @RequFiles = (@CurrRec * .15)ENDELSEBEGINSELECT @RequFiles = (@CurrRec * .25)END--make sure we have at least 5If @RequFiles < 5BEGINSELECT @RequFiles = 5End--*************************************STEP C Randomly select thatmany files**************************************--convert all variables to stringsSELECT @RequFilesSt = Convert(Varchar(100),@RequFiles)SELECT @CodeRevTypeSt = Convert(Char(1),@CodeRevType)SELECT @TodaySt = Convert(VarChar(10),@TodayNoTime,101)SELECT @SQLString = N'INSERT INTO TMP_UR_Randoms(Admit_DOCID,Client_ID, SelectDate, SelectType,RecordChosen)'SELECT @SQLString = @SQLString + N'(SELECT TOP ' + @RequFilesST + 'Admit_DOCID, Client_ID, SelectDate, SelectType, RecordChosen FROMFD__UR_Randoms 'SELECT @SQLString = @SQLString + N'WHERE SelectType = ' +@CodeRevTypeSt + ' AND SelectDate = ''' + @TodaySt + '''' + ' ORDERBY NEWID())'print @SQLStringexecute sp_executesql @SQLStringSELECT * FROM TMP_UR_Randoms/*--This select statement gives me what i want but I need to somehowmark these records and/or move this subset into the temp tableSelect Top @RequFilesFROM FD__UR_RandomsWHERE SelectType = @CodeRevType and SelectDate =Convert(varchar(10),GetDate(),101))ORDER BY NewID()*/ENDTHIS:GO
View 3 Replies View RelatedSQL Server 2012 :: Return Random Records In A Table-valued Function?
Dec 19, 2013My overarching goal is to generate sets of random Symptom records for each Enrollee in a drug study, so that for each cycle (period of time), the code will insert a random number of random records for each enrollee.
I'm trying to return a number of random records from a table, but inside a table-valued function... (which could be my problem).
CREATE FUNCTION dbo.ufn_GetTopSymptoms (
@enrollID INT
, @CTCVersion VARCHAR(20)
, @NumRecords INT
)
RETURNS TABLE
[Code] ....
But that ORDER BY NEWID() clause is illegal apparently, because here's the error it throws:
Msg 443, Level 16, State 1, Procedure ufn_GetTopSymptoms, Line 13
Invalid use of a side-effecting operator 'newid' within a function.
I was hoping I could return a set of enrollmentIDs and then use CROSS APPLY to generate a random set of records for each enrollmentID... is this not possible with APPLY? I was trying to avoid using a cursor...
The idea is basically to create all the Symptom records for all the patients in treatment cycle at once by using Enrollee OUTER APPLY dbo.ufn_GetTopSymtoms(dbo.Enrollment.EnrolleeID)
but that's clearly not working. Is there a way to do this without resorting to a cursor?
Reporting Services :: Select 10 Random Records From Past 30 Days For Each User
May 18, 2015I have these columns. TicketID, User, Date...I want to select 10 random tickets for each user for the past 30 days. I not sure whether to do this via sql or using VB in the report design.
View 5 Replies View RelatedSQL Server 2014 :: Selecting Records From Table 2 While Counting Records In Table 1
Aug 11, 2015Table1 contains fields Groupid, UserName,Category, Dimension
Table2 contains fields Group, Name,Category, Dimension (Group and Name are not in Table1)
So basically I need to read the records in Table1 using Groupid and each time there is a Groupid then select records from Table2 where Table2.Category in (Select Catergory from Table1)
and Table2.Dimension in (Select Dimension from Table1)
In Table1 There might be 10 Groupid records all of which are different.
Selecting 5 Records
Jan 25, 2002I have a database which has a field called fldTimes. basically this field records the number of hits a file gets. How can I choose the most 5 popular files with the greatest hits. Thanks
View 2 Replies View RelatedSelecting Next 50 Records
Sep 11, 2007Is it possible to use a SELECT to retrieve a Next 50 set of records?
Initial Query
Select top 50 email from table
What would the Query look like to retrieve the Next 50 records?
Thanks in advance.
Selecting Like Records
Aug 31, 2006This might be a simple question. I have a LIKE statement that is working fine, however I am not sure if something else is possible.
I can pull all records on a query for a person's name with a parameter value of "MARTIN". It will also pull records for "LYNN MARTIN". However, what if I would like to have that search also pull "LYNN M MARTIN"? Currently "LYNN MARTIN" is not finding "LYNN M MARTIN".
When the end user wants to search on LYNN MARTIN and that is what they input, I want SQL to find all records that match LYNN MARTIN and also find records that HAVE LYNN % MARTIN.
I hope this make sense? I guess I need to build my select statement using a WHERE LIKE statement, but I am not sure of the syntax.
Selecting Distinct Records
Sep 1, 2007Hi,
I'm just wondering if someone can help me with some SQL syntax stuff.I want to take this sql statement:
"SELECT TOP 50 tblProfile.chName, tblProfile.intCount FROM tblProfile, tblLinks WHERE (tblLinks.MemberID = tblProfile.MemberID) ORDER BY tblLinks.dtDateAdded DESC;"
and select only unique "chName's" records