Select Random Row Based On Weights

Feb 9, 2007

I have a table with two columns: value and weight (weight is smallint).

I want a query that will return the value of one row at random with the added requirement that rows with a higher weight have a higher probability of being chosen. Any ideas?

View 4 Replies


ADVERTISEMENT

Random Value From A Table Based On Timeframe ?

Nov 18, 2007

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 ?

View 5 Replies View Related

Select Random Row In Sql

Apr 11, 2007

Hi,
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..

View 8 Replies View Related

How To Do A Random Select

Nov 10, 2000

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 !

View 1 Replies View Related

Random Select

Nov 23, 2007

How Can I select a Random set of records from a database table?

View 3 Replies View Related

SQL: Sequential Random Select

Mar 8, 2006

Hello--I need to create a SQl statement (SQL SERVER 2000) which will do the following:

read table1 sequentially
select every "nth" record
write each selected record to and output table

Your help is greatly appreciated.
thanks,
MB

View 3 Replies View Related

Select 10 Random Records

Apr 22, 2008

Hello,

Is it possible to select 10 random records from a table?

Thanks,
Miguel

View 1 Replies View Related

SELECT A Random Row For Each Different Type

Jul 20, 2005

Hello all,I'm using SS 2000Based on the following table and data:CREATE TABLE T (F1 VARCHAR(1),F2 INT,F3 INT)/* T has no unique key *//* The data */INSERT INTO TSELECT 'A' AS F1, 1 AS F2, 10 AS F3UNION ALLSELECT 'B', 2, 12UNION ALLSELECT 'B', 3, 11UNION ALLSELECT 'A', 4, 10UNION ALLSELECT 'B', 3, 11UNION ALLSELECT 'A', 0, 0UNION ALLSELECT 'B', 2, 5UNION ALLSELECT 'D', 6, 7In the real situation, T is a subquery build from some tables and views. Icannot easily send the real problem.I want a query that will return:- One row for each different F1, no matter which it is.- Ideally, no temporary table should be created. Only subquery.- F2 and F3 must come from the same row. So the use of aggregate functionsto retreive each of them cannot be used.A possible result could be:F1 F2 F3---- --------- -----------A 1 10B 2 12D 6 7How could I do that?Thanks for your helpYannick

View 2 Replies View Related

How Can Select Random Rows From Database?

Jan 23, 2008

Hi
Iam developing online test using ASP.NET,C#.NET.Now my doubt is test takers should get random questions from the database sqlserver2005.Already i ve inserted 10 question into the database.I ve used NEW ID() .But questions are repeating.I am new commer in to IT industry .Provide me with code.Given below is my code.I can retrive questions from the database now but cannot get in random order.protected void Page_Load(object sender, EventArgs e)
{Label10.Visible = false;
Label9.Visible = false;if (!IsPostBack)
{if (Session["id"] == null)
{Session["id"] = 1;
}Session["ans"] = 0;SqlConnection con = new SqlConnection(@"server=123-9181FF31362SQLEXPRESS;user id=sampleu;pwd=Sampleu2;database=n;");
con.Open();
SqlCommand cmd = new SqlCommand("select * from n3 where sno='" + Convert.ToInt16(Session["id"].ToString()) + "' ", con);SqlDataReader re;
re = cmd.ExecuteReader();while (re.Read())
{
 
Label1.Text = re[0].ToString();
Label2.Text = re[1].ToString();
Label3.Text = re[2].ToString();
Label4.Text = re[3].ToString();
Label5.Text = re[4].ToString();
Label6.Text = re[5].ToString();string sss = re[6].ToString();if (sss == "nil")
{
Panel1.Visible = true;Panel2.Visible = false;
}
else
{Panel1.Visible = false;Panel2.Visible = true;
}
}
re.Close();
}
}
 
 protected void Button1_Click(object sender, EventArgs e)
{string Answer = "";if (RadioButton1.Checked == true)
{Answer = "A";
}else if (RadioButton2.Checked == true)
{Answer = "B";
}else if (RadioButton3.Checked == true)
{Answer = "C";
}else if (RadioButton4.Checked == true)
{Answer = "D";
}else if (RadioButton5.Checked == true)
{Answer = "True";
}else if (RadioButton6.Checked == true)
{Answer = "False";
}SqlConnection con1 = new SqlConnection(@"server=123-9181FF31362SQLEXPRESS;user id=sampleu;pwd=Sampleu2;database=n;");
con1.Open();
string ss = Session["id"].ToString();SqlCommand cmd1 = new SqlCommand("select Answer from n3 where sno='" + Convert.ToInt16(Session["id"].ToString()) + "' ", con1);
SqlDataReader re1;string result1 = "";
re1 = cmd1.ExecuteReader();if (re1.Read())
{
result1 = re1[0].ToString();
}
//Session["ans"] = 0;if (Answer == result1)
{Session["ans"] = (Convert.ToInt16(Session["ans"].ToString()) + 1);
 
}
con1.Close();
 Session["id"] = (Convert.ToInt16(Session["id"]) + 1);
RadioButton1.Checked = false;RadioButton2.Checked = false;
RadioButton3.Checked = false;RadioButton4.Checked = false;
RadioButton5.Checked = false;RadioButton6.Checked = false;
 
 SqlConnection con = new SqlConnection(@"server=123-9181FF31362SQLEXPRESS;user id=sampleu;pwd=Sampleu2;database=n;");
con.Open();SqlCommand cmd = new SqlCommand("select * from n3 where sno='" + Convert.ToInt16(Session["id"].ToString()) + "' ", con);SqlDataReader re;
re = cmd.ExecuteReader();
 
 
 while (re.Read())
{
Label1.Text = re[0].ToString();
Label2.Text = re[1].ToString();
Label3.Text = re[2].ToString();
Label4.Text = re[3].ToString();
Label5.Text = re[4].ToString();
Label6.Text = re[5].ToString();string sss = re[6].ToString();if (sss == "nil")
{
Panel1.Visible = true;Panel2.Visible = false;
}
else
{Panel1.Visible = false;Panel2.Visible = true;
}
}
re.Close();int s = Convert.ToInt16(Session["id"].ToString());if (s == 11)
{
Label10.Text = Session["ans"].ToString() + " / " + "10";Label9.Visible = true;
Label10.Visible = true;
//int x = Convert.ToInt16(Session["ans"].ToString());
//int y = (x/10) * 100;//y = Label10.Text;
 
}
////// SqlConnection con6 = new SqlConnection(@"server=123-9181FF31362SQLEXPRESS;user id=sampleu;pwd=Sampleu2;database=n;");
////// con6.Open();
////// SqlCommand cmd6 = new SqlCommand("SELECT CONVERT(int, 10*RAND()) from n3 where sno='" + Convert.ToInt16(Session["id"].ToString()) + "' ", con6);
////// SqlDataReader re6;
////// re6 = cmd6.ExecuteReader();
////// //select top 5 * from Employee order by NEWID()
//////// SELECT FirstName,LastName
////////FROM Person.Contact
////////TABLESAMPLE SYSTEM (10 PERCENT)
 
////// while (re6.Read())
////// {
////// Label1.Text = re6[0].ToString();
////// }
////// con6.Close();
}
}

View 3 Replies View Related

Select Random Records From MS SQL Table?

Aug 26, 2005

Hello,I have 1000 of records in my table. I wanna select random 100 of them? How?regards

View 3 Replies View Related

Select At Random Order From Database

Dec 23, 2005

Hi,
I need to select few items from sql database.I know for ORDER BY, but I need those items to be mixed in random order every time when they are returned from database. Those are the same items every time, just randomly mixed.
Can i do it with SQL, or I have to find another way (e.g. to mix them after sql returns them)?

View 1 Replies View Related

How Do I Select A Random Record From The Table

Sep 18, 2000

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

View 1 Replies View Related

How To Select Random Row In Table Using Sql Server...

Jun 12, 2007

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

View 4 Replies View Related

How To Select Random Records From Database (advanced)

Mar 12, 2006

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

View 17 Replies View Related

Random Selection From Table Variable In Subquery As A Column In Select Statement

Nov 7, 2007

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

View 14 Replies View Related

Reporting Services :: Select 10 Random Records From Past 30 Days For Each User

May 18, 2015

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.

View 5 Replies View Related

Reporting Services :: Select Text Field Dataset Based On User Select Option?

Aug 4, 2015

I have a report that uses different datasets based on the year selected by a user.

I have a year_id parameter that sets a report variable named dataset_chosen. I have varified that these are working correctly together.

I have attempted populating table cell data to display from the chosen dataset. As yet to no avail.

How could I display data from the dataset a user selects via the year_id options?

View 4 Replies View Related

Returning Random Records And NOT Similar (random Questions)

Jul 20, 2005

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 Related

How To: Create A SELECT To Select Records From A Table Based On The First Letter.......

Aug 16, 2007

Dear All
I need to cerate a SP that SELECTS all the records from a table WHERE the first letter of each records starts with 'A' or 'B' or 'C' and so on. The letter is passed via a parameter from a aspx web page, I was wondering that someone can help me in the what TSQL to use I am not looking for a solution just a poin in the right direction. Can you help.
 
Thanks Ross

View 3 Replies View Related

URGENT - Random 10 From Random 20 (2 Tbls)

Oct 14, 2004

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.

View 1 Replies View Related

Select Based On Nonexistant Value

Jan 21, 2004

Hey all, I'm having an interesting problem, but also keep in mind I am certainly a newbie with DB's and SQL but here's what I got:

I'm making an application that creates quotes for our customers. I want to create a Quote Number that is based on the largest of the last number in the DB but only based on that company. For example:

CoID QuoteNum
123 1
123 2
666 1
123 3
666 2
666 3

so if I created a new quote for CoID: 666, the QuoteNum would be 4. Easy enough. But the problem arises when I create a new quote for a NEW COMPANY, one who's CoID is not already present in the DB. So what I have so far is this:

SELECT QuoteNum FROM Quote
WHERE QuoteNum = (SELECT MAX(QuoteNum) FROM Quote
WHERE CoID='222')

but since 222 does not yet exist I get an error: "Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record."

So how do I tell the DB to ignore that error and perform another task should it arise? Any help would greatly be appreciated. THANKS!

View 4 Replies View Related

SQL 2012 :: SET CMD Value Based On Select Name

Oct 21, 2015

I am trying to move files to directories based on the file status. If the file contains the Flag='E' then I want to move it to an "Error" folder, otherwise it will go to a "Processed" folder.

Here is my current code:

DECLARE @cmd varchar(500)
SELECT
CASE
WHEN Processed_Flag = 'E'
THEN
SET @cmd='MOVE /Y C: empmyfiles*.dat C: empmyfilesError'
ELSE
SET @cmd='MOVE /Y C: empmyfiles*.dat C: empmyfilesProcessed'
END
FROM#TEMP

PRINT @Cmd

View 2 Replies View Related

Select Statement Based On A Date

Feb 19, 2004

i am not getting a result back when i run the query below.

select * from users where DateCreated = '2004-02-19'

so i went into the table and looked at the record. for DateCreated field i have both date and time. ex: 2004-02-19 08:40:00

how can i select this record with out using the time in the select statement. what i want to see is how many users signed up for a day. any ideas?

View 5 Replies View Related

Updating Based On A Select Iteration

Jan 8, 2001

Okay I'm going nuts.

I have a table with a max key value in it, and another table with a few rows in it. I'm trying to update the two new rows with key values that are iterative from the MAX value in the first table. Could anyone point me to some good LOOP...UPDATE, etc resources or pointers before I go postal?

::grins::

Example: first table has a key field, last value in it is say 1000. The second table has two records. They need keys too, and those keys need to start at the last value in the first table + 1, so 1001, and end on the last row of the second table, or, say 1002. I cannot figure out how to read the last value of the first table, and create an update loop to iteratively update the key value in the second table based on the max value of the first, and looping based on the number of records in the second, in this case 2.

Arrrg!


~EHunter

View 4 Replies View Related

Select Query Based On Datetime

Feb 12, 2002

I need to select certain rows based on a "datetime" column. I need to select rows from 8am yesterday until 8am today.
In Oracle I would use:
select * from foo where TIMESTAMP >= trunc(sysdate - 1) + 8/24 AND TIMESTAMP < trunc(sysdate) + 8/24.
This would start at 8am yesterday and end at 7:59am today.

How would I do this with T-SQL?

thank you,

Mark
Fiesta_donald@email.com

View 1 Replies View Related

How To Do Update Of Select Columns Based On...

Jun 21, 2007

the following criteria.
i have the selection all done but am trying to figure out how to do the following:
if column4 < 0 then add column4 to column3, move 0 to column4;
if column3 < 0 then add column3 to column2, move 0 to column3;
if column2 < 0 then add column2 to column1, move 0 to column2;
add column3 to column4;
move column2 to column3;
move column1 to column2;
if column0 > 0 move column0 to column1, move 0 to column0 else move 0 to column1;

these are all numeric data types.

View 7 Replies View Related

Select Record Based On Max Date

Sep 12, 2012

I have the following table:

Occ_Num Feature_Num Trans_Date Peril_Desc
123 1 1-2-2012 Water
123 1 1-11-2012 Ice
123 2 1-2-2012 Other
123 2 1-13-2012 Other
123 2 1-19-2012 Wind

I want to select each Occ_Num, Feature_NUM, Trans_Date, and PERIL_Desc but with only the Peril that was part of the max trans_date.

So i would want the following from above:

Occ_Num Feature_Num Trans_Date Peril_Desc
123 1 1-11-2012 Ice
123 2 1-19-2012 Wind

I'm having trouble with the syntax need to accomplish this.

View 2 Replies View Related

Select Product Based On Date?

Sep 23, 2012

Product Date
--------------------------------------------------------
pd0 2012-08-11 18:45:55.780
Pd1 2012-08-11 18:55:17.020
pd2 2012-08-11 19:06:58.623
pd3 2012-08-18 12:00:01.193
pd4 2012-08-25 12:13:04.077
pd5 2012-08-25 17:28:30.347
pd6 2012-08-25 18:23:16.473
pd7 2012-09-18 18:29:58.360

I want select the product based on from date and to date.

For Example

I want the select the product date in between 2012-08-11 to 2012-08-18

Note:dont check the time.

I want the query for select product based on only date not depend upon time

View 6 Replies View Related

Select Latest Date Based On ID

Dec 3, 2012

I want to select the latest single value based on ProductID. The value should be the last value inserted from the Date column.

I want to use this as part of a query to delete this value when a button is slected in ASP.Net C#.

Here is my code which does not work

select basketID, SessionID, UserID, MAX(Date), productID from ShoppingBasket
where userID = '1'
group by productID, basketID, SessionID, UserID

View 2 Replies View Related

How To Create Set Of Records Based On SELECT

Oct 10, 2014

I have a set of records that will always be the same. A template if you wanna call it that.

"SELECT ID, Field1, Field2, Field3, Field4, Field5, Field6, Field7 FROM TABLE WHERE Field2 = 000"

This returns a 20 record set. The user will click a button and create another set of 20 records, where they can make changes accordingly.

View 8 Replies View Related

Create Function Based On Select

Mar 3, 2015

I have select to split FullName on LastName and FirstName columnselect

Substring(FullName, 1,Charindex(',', FullName)-1) LName
,Substring(FullName, Charindex(',', FullName)+1, LEN(FullName)) FName
from Table1

Is it possible to create function based on that select? If yes. How it to do?

View 12 Replies View Related

Add A Column Based On A Select Query

Mar 5, 2006

Supose I have the following select:

Select Name, Age, (select TelNums from Telephone)
From Person

The problem is that (select TelNums from Telephone) can return more than 1 record:

tel1
tel2
.
.
.

I was wondering how I can make a select to return the tel numbers like: 'tel1,tel2,tel2'

»»» Ken.A

View 3 Replies View Related

Select Records Based On Number

Mar 8, 2006

I'm trying to select records that occur more than once. I'm trying to base this on the email column. So basically I want the query to look something like this:

select * from table where emailaddress count > 1

Can someone provide me with the correct syntax? :)

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved