When I execute the following query several times, I get the same row if there is no new data inserted:
SELECT TOP 1 * FROM TableName
Is there a way to get a random row from the table? Thanks in advance.
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
View 1 Replies View RelatedHi,I am in a situation where our developer is on leave (annual leave for a month), and I have to add a control to my website, which is in aspx apges.To start with i have created a table in my SQL database. this table has records with one-liners from various movies, and the movie title. The tabel have 3 columns, i.e. ID, Liners, MTitle.So i want to get random records on the page when ever its refreshed. I am totally non-coder/programmer guy. I have got a SQL statement from the internet " SELECT TOP 1 * FROM <table name> ORDER By NEWID() "So would anyone please help me out with it, as is it correct, how can i apply in the aspx pages. Thank you.
View 2 Replies View RelatedHi,I have a form that should show 2 pictures based on table entries.I want those 2 pictures to be randomly selected based on a database table. So, my table has all the entries, and I want to pull out a random entry that has been approved to display it.Can someone help me with the sql query?I can do SELECT VoteId FROM tblVotes WHERE Approved=True..But how do I make selection a random one that changes every time the user gets another entry?
View 2 Replies View RelatedHello,I have 1000 of records in my table. I wanna select random 100 of them? How?regards
View 3 Replies View RelatedHello.
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.
Hello,
I need to write a select statement where I need to select the record randomely. Is there any function to do that? What's the best way to do that? Please help...
Thanks in a million.
Sarika
Hi all!
I want to fill a table with random values. In each line should be a different value - "independet" from the value the row above.
what i tried didnt work, it always produced the same value.
Maybe you have an idea
Thankx in advance and greetings from Vienna
Landau
I Got the Sql query "Select Top 1 * from Books order by NewId()"........ for select Random row in a table .. any one can explain Above Query
Regards,
S.sajan
Hello Everyone,
I want to get a random value from a table only once a day, not on every page load.
With this SQL I can get a random value and everytime it gives me new value....
SELECT TOP 1 PRODUCTID
FROM PRODUCT
ORDER BY NEWID()
How the above query can be changed or there can be some sort of trigger set that same value for 24 hours....and it changes after that ?
Hello,
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.
hi
I have a field call: password, I want to generates some random chars as
default password for users (so user got something to login, and update
later).
Current Solution I have:
I made myself a trigger and it does generate password on insert, but
the problem with that is I have to allow NULL on that field. I
don't want NULL to be allowed, and if I don't allow null... sql server
won't allow me to insert a record (when leaving password field empty).
Reason i don't wanna allow NULL on that field, is because there may be
more applicaions using the same field from the same database.
Just in case if the other developer gets sloopy, I don't want other
application(s) allow user put to enter a null or leave it blank on my
password field.
My question is:
1) can I put a function or some sort at the "Default" field under "Design Table", if so...how?
2) any suggestions for a better implementation?
3) if (1) doesn't work, what other options would you suggest other then the current trigger i'm using?
TIA
We have a SQL Server 2008R2 system that has heavy usage to one specific table. I have tuned basically all I can as far as making sure SQL Statements are using good indexes. From time to time a group of folks will log into Mgt Studio and run SQL Statements like this, leave the query open and once in a while it will cause blocking to other SQL running our online system
The query is like this: select ID,* from tablename with (nolock) where ID like 'MSPRYy%'
The results come back within less than 1 second. However, they leave this window open which is what causes this to be a HEAD BLOCKER and blocks other SQL Statements from running.
I am using Sql Server 2008 R2.I have a existing query that basically says
Select Top 50 Subscriber_ID, Member_Name, Group_ID
from my_table
order by rand(checksum(newid()))
However the client now wants to have at least two from each group_id. There are 17 different groups. When I run this as is I get about six of the 17 groups in the results. How can I change this to get at least two results from each group_id?
I'm using ASP and SQL Serv 2000. What I need to get from 2 tables (company & customers) is random 10 customers from random 20 comp.
Anyone got an idea how to do this??? I've spent 2 days trying to get stored proc. or T-SQL to work, but nothing good came out of it. I can get 1 comp and 10 cust, but not a grouped list of 20 comp. w/ 10 cust. each.
Help is greatly appreciated.
I'm grappling with this issue which I thought was basic VB programming; I'm trying to insert a random number (between 100 and 999) into a SQL table column (=Status_ID). This is input as part of a user submitting helpdesk requests via a APS.Net Web Form. The 'Status_ID' field is obviously not visible to the user but will help reference this Helpdesk request on the database.Here is the code:Protected Sub submitButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles submitButton.Click If Page.IsValid Then ' Define data objects Dim conn As SqlConnection Dim comm As SqlCommand ' Read the connection string from web.config Dim connectionString As String = _ ConfigurationManager.ConnectionStrings("ITNet_Students").ConnectionString ' Initialize connection conn = New SqlConnection(connectionString) ' Create command comm = New SqlCommand( _ "INSERT INTO HelpDesk (First_Name, Last_Name, StudentID, PersonalEmail," & _ "CategoryID, SubjectID, Description, StatusID) " & _ "VALUES (@First_Name, @Last_Name, @StudentID, @PersonalEmail, " & _ "@CategoryID, @SubjectID, @Description, @StatusID)", conn) ' Use randomize Randomize() Dim randomvalue As Integer ' Generate random value between 999 and 100. randomvalue = Int((900 * Rnd()) + 100) ' Add command parameters comm.Parameters.Add("@First_Name", System.Data.SqlDbType.NVarChar, 50) comm.Parameters("@First_Name").Value = fnameTextBox.Text . . . comm.Parameters.Add("@StatusID", System.Data.SqlDbType.Int) comm.Parameters("@StatusID").Value = randomvalue 'Enclose database code in Try-Catch-Finally Try ' Open connection conn.Open() ' Execute the command comm.ExecuteNonQuery() ' Reload page if the query executed successfully Response.Redirect("HelpDesk.aspx") Catch ' Display error message dbErrorMessage.Text = _ "Error submitting the help desk request! Please try again later, and/or change the entered data!" Finally 'close connection conn.Close() End Try End If End Sub----------------------------------------------------------------------------------------------------------------------I keep getting the error message under 'Catch' and the page 'HelpDesk.aspx' is not reloading; the 'comm.ExecuteNonQuery()' is not executing.Can anyone spot any inconsistencies in the declaration of the 'randomvalue' variable?P.S: this code works fine if you replace 'randomvalue' with any integer in 'comm.Parameters("@StatusID").Value = randomvalue'
View 2 Replies View RelatedHello,
I need to sample data in a very large table in SQL Server 2000 (a gazillion rows of Performance Monitor statitics).
I'd like to take the top 5%, for instance, based upon a column containing random numbers.
Can anyone suggest a highly efficient method of populating a column with random numbers.
Thanks in advance.
Rod
I'm trying to write a function that will retrieve the last backup date/time of a particular database on a remote server (i.e. by querying msdb.dbo.backupset). Unfortunately, you can't use sp_executesql in a function, so I can't figure out a way to pass the server name to the query and still be able to return the datetime value back to the calling TSQL code (so that rules out using EXEC().
View 3 Replies View RelatedIm from Russia, sorry if my english is not very good.
Here's the case:
1)-------------------------------
I made a DTS-package in sql2000 that transfers the [sql table] into [dbf file] via jet4.
First i create (in delphi) the empty dbf with the same name and columns same as in sql table.
Second, I run my DTS with variables - source and destination table names
In DTS there is source, destination and transformation . After I send the Variables(table names)
, the transformation "arrow" needs to be "refreshed" to make column names in both tables correspond each other. For that in transformation I chose ActiveXScript Mode and wright VB Script:
'**********************************************************************
' Visual Basic Transformation Script
'************************************************************************
' Copy each source column to the destination column
Function Main()
dim i
For i = 1 To DTSSource.Count
DTSDestination(i) = DTSSource(i)
next
Main = DTSTransformStat_OK
End Function
And it works
2)------------------------------
I want to do same thing in sql2005 SSIS but don't figure out how...
I managed to make a package that recieves (in variables) table names and runs correctly.
But after I change those variable names into any other it crashes -
Description: "component "OLE DB Source" (1)" failed validation and returned validation status "VS_NEEDSNEWMETADATA".
Of cource this happens 'cause I didn't "refresh" the transformatoin (and maybe also source and dest), but I don't know how.
Anyone can help ?!
I'm trying to add random dates to date column in existing table, but these need to be week days (Mon-Fri).I'm a beginner in TSQL, worked with MS Access many years - in Access I used to do something a bit different:
DateAdd("d",(Int((5*Rnd([ID]))+1)),#31/08/2015#)
Table had ID, I gave a date it would start from (31/08/2015) and then range of ID to apply new date:
UPDATE table1 SET table1 .date = DateAdd("d",(Int((5*Rnd([ID]))+1)),#31/08/2015#)
WHERE (((table1 .ID) Between 1 And 5456));
This was applying random dates in range of 31/08/2015 + 5 days, so I could give a starting date of Sunday to get random dates populated over given IDs from Monday to Friday.Now, how can I do it in TSQL?I have a table with ID and dates column. I would like to apply new random dates from some range, but making sure they will be week days.
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 this table
CREATE TABLE [Sales].[Test_inmem]
(
[c1] [int] NOT NULL,
[c2] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ModifiedDate] [datetime2](7) NOT NULL CONSTRAINT [IMDF_Test_ModifiedDate] DEFAULT (sysdatetime()),
[Code] ....
I have to generate 1000000 random records into it. I tried various ways to insert records, but not being a developer could not do it. I hope to make the C1 as a serial number, C2 can be anything, C3 I want to be the timestamp.
Consider the below code: I am trying to find a way so that my select statement (which will actually be used to insert records) can randomly place values in the Source and Type columns that it selects from a list which in this case is records in a table variable. I dont really want to perform the insert inside a loop since the production version will work with millions of records. Anyone have any suggestions of how to change the subqueries that constitute these columns so that they are randomized?
SET NOCOUNT ON
Declare @RandomRecordCount as int, @Counter as int
Select @RandomRecordCount = 1000
Declare @Type table (Name nvarchar(200) NOT NULL)
Declare @Source table (Name nvarchar(200) NOT NULL)
Declare @Users table (Name nvarchar(200) NOT NULL)
Declare @NumericBase table (Number int not null)
Set @Counter = 0
while @Counter < @RandomRecordCount
begin
Insert into @NumericBase(Number)Values(@Counter)
set @Counter = @Counter + 1
end
Insert into @Type(Name)
Select 'Type: Buick' UNION ALL
Select 'Type: Cadillac' UNION ALL
Select 'Type: Chevrolet' UNION ALL
Select 'Type: GMC'
Insert into @Source(Name)
Select 'Source: Japan' UNION ALL
Select 'Source: China' UNION ALL
Select 'Source: Spain' UNION ALL
Select 'Source: India' UNION ALL
Select 'Source: USA'
Insert into @Users(Name)
Select 'keith' UNION ALL
Select 'kevin' UNION ALL
Select 'chris' UNION ALL
Select 'chad' UNION ALL
Select 'brian'
select
1 ProviderId, -- static value
'' Identifier,
'' ClassificationCode,
(select TOP 1 Name from @Source order by newid()) Source,
(select TOP 1 Name from @Type order by newid()) Type
from @NumericBase
SET NOCOUNT OFF
I'm importing comma-delimited text files into a SQL table. The data imports in a seemingly random order. One time I import and the lines appear one way and the next time I import they import another way.
Is there a way to force the text files to import in the same order the data is found in the file?
I have a table with a column ID of ContentID. The ID in that column is all NULLs. I need a way to change those nulls to a number. It does not matter what type of number it is as long as they are different. Can someone point me somewhere with a piece of T-SQL that I could use to do that. There are over 24000 rows so cursor change will not be very efficient.
Thanks for any help
Hi there.I need to fetch ONE random row from many. How can i get it? I try toexecute "SELECT field.table FROM table ORDER by RAND()" no effect.Thank you.
View 1 Replies View RelatedHi,
i wanna fetch random data using sql query. is there any query that returns random row? Please help me.. i found some but they didnt work..
Hy,
I nead to make a stored procedure in order to add a random record set.
This record set is used to write in an ASP page a list which is automatically modified everytime we reload the page.
Thanks !
Hi,
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
Has anyone noticed that if you created DTS package and try to
change connection properties (i.e. change DSN or redirect
DTS to different server or Database), as soon you click OK
it does not save new password and hence does not work
anymore. In my case to move Database from Development to
Production server I would have to recreate all DTS packages.
Is there any way around it?
Any ideas greatly appreciated.
I am trying to get random numbers to have a unique value for different processes, then I can identify each process. What happens is that I use rand() function without seed, so I got my random numbers, but after shutting down SQLServer and try to get again another random number after booting up, the same series of random numbers is given again and again. So if anyone knows how I can get unique values,even though reseting the server, and using random function or any other method which automatically provides unique values,I'll really appreciate it if you let me know it.
This is the function: select rand()
Alberto.
Is there a way to write an SQL statement to choose random values from a particular select statement..for example:
select * from address
I was thinking, if there was a way to use an include statement for it like:
select * from address where id IN ('generates some random values')
thanks in advance
So i've got to generate these queries that go from the first of a one month to the last day of a month. the user provides the starting and ending months.
What I was wondering was is there an easy way to set the last day of the month in sql without having to go in and hard code which months end on the 30 and which ones end on the 31.