SQL Union Problems When Trying To Retrieve Random Records
Jun 6, 2006
I have a situation where i am trying to run two query's and retrieve
one record from each query, then using Union combine them into one
recordset. The First Query is strait forward and should just return
the matching record however the second query needs to find a random
record and return it. The union is causing me some trouble. It seems
that 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 smallint
declare @variable2 int
set @variable1 = 10002
set @variable2 = 1001211720
select col1, col2, col3, col4, col5, col6
from table1
where col1 = @variable1 and col2 = @variable2
union
select col1, col2, col3, col4, col5, col6
from table2
where col2 = @variable2
order by newID()
--------------------------------------------------------------------------------------------------------
I have also tried the following which does not provide an error but i
cannot find a way only to return one record from the second query
--------------------------------------------------------------------------------------------------------
declare @variable1 smallint
declare @variable2 int
set @variable1 = 10002
set @variable2 = 1001211720
select newID() as colID, col1, col2, col3, col4, col5, col6
from table1
where col1 = @variable1 and col2 = @variable2
union
select newID() as colID, col1, col2, col3, col4, col5, col6
from table2
where col2 = @variable2
order by colID
If 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 know
if you need more of an explination.
Hi,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
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
Hi 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
hi 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
hi 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 &""
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
I 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
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
Once 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?
I'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
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?
I'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
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.
Dear 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
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.
I 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!
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..
hello 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
I'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
SELECT row_number() over (ORDER by a.empid) as rec_num, empname FROM employee_a UNION SELECT row_number() over (ORDER by a.empid) as rec_num, empname FROM employee_b
the problem is the rec_num repeat for each statement like this :
rec_num empname
1 john 2 maggy 1 lee 2 mary 3 louis
How do i make the rec_num continue for the next statement after union.
My 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?
I 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.
I have an application that has an existing query which returns org units (parent and child) from organization table with orderby on createddate + orgid combination.
Also I added another log table Organization_log with exact columns as Organization table and additional 'IS_DELETED' bool column.
WITH Org_TREE AS ( SELECT *, null as 'IS_DELETED', convert (varchar(4000), convert(varchar(30),CREATED_DT,126) + Org_Id) theorderby FROM Organization WHERE PARENT_Org_ID IS NULL and case_ID='43333'
[code]...
I need to modify the query:
1. To display the records both from the Organization table and Organization_Log table. 2. The orderby should be sorted on 'Organization Name' asc and it should follow the child order in alpha sort as well.
I am trying to write a query that will retrieve all students of a particular class and also any rows in HomeworkLogLine if they exist (but return null if there is no row). I thought this should be a relatively simple LEFT join but I've tried every possible combination of joins but it's not working.
SELECT Student.StudentSurname + ', ' + Student.StudentForename AS Fullname, HomeworkLogLine.HomeworkLogLineTimestamp, HomeworkLog.HomeworkLogDescription, ROW_NUMBER() OVER (PARTITION BY HomeworkLogLine.HomeworkLogLineStudentID ORDER BY
[Code] ...
It's only returning two rows (the students where they have a row in the HomeworkLogLine table).
I am using ADODB.Recordset to retrieve the records from an MS Access database. The table has 11450 records and the recordset shows this number as the recordcount too. But when I try to fetch the records in a While loop using EOF as the check, I am able fetch only 4000 or so records. No error occurs and no exception is thrown. Control just comes out of the function. Why could this be happening? Does Recordset have some limit on the number of records for Jet 4.0 provider?
We all were new at one point.... any help is appreciated.
Objective:
Combining two 49,000 row tables and remove records where there is only 1 column difference. (keeping the specified column value removing the one with a blank.)
Reason:
I have 2 people going through a list, coding a specific column with a single letter value. They both have different progress on each sheet. Hence I am trying to UNION them and have a result of their combined efforts without duplicates.
My progress/where I'm stuck:
Here is my first query/union:
SELECT * FROM [Eds table] UNION SELECT * FROM [Vickis table];
As shown above, I have unioned these 2 tables and my results removed th obvious whole record duplicates, but since 1 column is different on these, a union without criteria considers them unique.....
an example of duplicates that I must remove are as follows:
