I need to build an SQL query, which generates the random rows priority wise.
I found this on following article.
http://articlesdotnet.blogspot.com/2007/09/random-rows-in-respect-to-their.html
Can anyone test it?
In addition, tell me.
Is it good or have a bug?
If anyone have the better query then please tell me.
I've been presented with a task to do a query similar to the followingand I was curious as to what the quickest query would look like.Anyone have any ideas??Some_Id Value1 A1 B1 C2 C2 A2 B3 B3 C4 C5 Q5 C5 R6 T7 P7 BThe problem is that I want to select one record for each ID. If arecord with the value of 'A' exists, then I want to select that recordfor that ID. If not, I want to select the record with the value 'B'for that ID if it exists. Otherwise, just give me the first record forthat ID that exists. The result set would look like this:Some_ID Value1 A2 A3 B4 C5 Q6 T7 BThanks for your input!
View 12 Replies View RelatedAre there any way to execute a procedure and return N random rows?
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();
}
}
As a part of my unpaid internship, I am creating a ASP.NET interface for a MS SQL Server 2000. The table I am having problems with has over 750,000 rows by 26 columns. There isn't a primary key. It stores a transaction dump from another primitive Database server.
Problem:
When perform a query I get one set of results. I run the same query again after a short wait and the rows returned are in a different order. The majority of the rows returned are the same ones returned in the previous query. Only, some rows may be missing and the order may change.
I really wasn't surprised by some duplicate rows. I am confused why they are showing up in a different order and the above mentioned inconsistancy in results.
I thought I was having problems with my Repeater Control; however, SQL Query Analyzer returns the same results.
Question:
What is causing the problem?
I have 2 tables, Artists and Artworks.
I have a query:
SELECT TOP (4) dbo.Artists.ArtistID, dbo.Artists.FirstName + ' ' + dbo.Artists.LastName AS FullName, dbo.Artworks.ArtworkName, dbo.Artworks.Image
FROM dbo.Artists INNER JOIN
dbo.Artworks ON dbo.Artists.ArtistID = dbo.Artworks.ArtistID
ORDER BY NEWID()
This query returns random images, but the artists are sometimes repeated.
I would like to have DISTINCT Random Artists returned, each with a random image. I tried various subqueries, but I just get error messages.
Any help would be appreciated.
Thnks,
Paolo
I have a text file that come from our client that is Column deliminated by ~ and row deliminated by {CR}{LF}.
There is a comment field that appearently is not cleaned up and has {CR}{LF} within the comment field.
I am new to SSIS and I'm wondering if there is a way to detect and correct the bad rows?
example file formet:
ORDERID~DATE~Comment~Address
1~2/3/2007~Some Comment~1234 oak st
2~2/3/2007~Some messed
up comment~345 oak st.
3~2/3/2007~Another comment~3214 asdf blvd.
Thank you.
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 have to Make Department Table.
But this there names can be in multi language.
--------------------------------------------DepartmentGroup--------------------------------------------DepartmentGroupId (P.K.)UniqueNameCreatedDateModifiedDate--------------------------------------------
--------------------------------------------Department--------------------------------------------DepartmentId (P.K.)NativeNameNavigateUrlIsFavoriteLanguageId (F.K.)DepartmentGroupId (F.K.)IsVisibleCreatedDateModifiedDate--------------------------------------------
From this design we can move to same department in different language.
is it right?
Hi
I have to delete the value of acktitle where ackby = null
I tried using update but its not helping me i tried doing
update incidents(table name)
set acktitle = null where ackby = null
its not giving me any results thats why i thought of delecting but delete syntax removes the whole row
How should i go about it
Our databases were working fine in terms of performance until over the last 2 months wherein the timeouts and deadlocks have started increasing. We are having growing clients with huge real-time transactions. We have been adding NOLOCKs on queries that are being used frequently etc but the timeouts are still an issue. Not sure what the best way to identify problem areas is. The CPU utilization has also been on the higher side. Profiler identified long queries are being indexed etc. But the performance is still a concern. Any ideas? What am i not looking at? HELP HELP..
Thanks,
Sri
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 RelatedIm using , FORMAT_STRING="###,###.##" on the definition of a calculated member. When testing the query in SSMS, the formatting looks right, but when I run the query in SSRS it looks like its not coming over. I suppose I can apply my own formatting on the report, but im just wondering if this is by design or lack of ?
View 3 Replies View RelatedI have a query in and OLEDB Source which results in incorrect rows returned due to its structure:
SELECT table1.ABCD, table2.BAAA, table3.CAAA, table4.DAAA, table5.EAAAFROM table1LEFT OUTER JOINtable3 ON table1.ABCD = table3.BCDE LEFT OUTER JOINtable4 ON table1.ABCD = table4.CDEF LEFT OUTER JOINtable5 ON table1.ABCD = table5.DEFG LEFT OUTER JOINtable2 ON table1.ABCD = table2.EFGHWHEREtable1.extractSession = ?AND table3.extractSession = ?AND table4.extractSession = ?AND table5.extractSession = ?AND table2.extractSession = ?
The correct query needs to be the following, but it won't work in the OLE DB Source:
SELECT table1.ABCD, table2.BAAA, table3.CAAA, table4.DAAA, table5.EAAAFROM table1LEFT OUTER JOINtable3 ON table1.ABCD = table3.BCDE AND table3.extractSession = ?LEFT OUTER JOINtable4 ON table1.ABCD = table4.CDEF AND table4.extractSession = ?LEFT OUTER JOINtable5 ON table1.ABCD = table5.DEFG AND table5.extractSession = ?LEFT OUTER JOINtable2 ON table1.ABCD = table2.EFGH AND table2.extractSession = ?WHEREtable1.extractSession = ?
=========================
ExtractSession is an integer that uniquely identifies the run (for the night, perhaps). I load a bunch of staging tables that retain their data for a period of time, with each load identified by this staging number. So, I need to restrict my data pull to the correct load (extractSession).
The first query returns three (3) rows when I should be getting back all 250,000 rows from table1. The second query listed works correctly.
Am I missing something, or do I need to find another way to constrain my tables' extract session dynamically at execution time in SSIS? Is a control table the best way to go here and simply join to it?
Thanks,
Phil
Hi,
I need to pass data from a SQL Server data base to an Access data base. To do this I use the OPENROWSET as followed:
FR
INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'C:Aux.mdb'; 'Admin'; '',Test) (Id, Name, TypeId) SELECT Id,Name,TypeId
FROM Test
ORDER BY TypeId
FR
On SQL Server 2000 or MSDE the data is transfered as expected, respecting the specified order. But when I run the same clause on a SQL 2005 EE the data is transfered, but the order is not respected.
So my question is if I have to activate an option for the order to be respected or if this is a bug.
Best regards,
Ângelo Vilela
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.
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.
Hello guys , I am new here,
Well, I am moving my greetings script from MS Access to MS SQL, here is what I was using in MS Access.
See this screenshot: Click Here
Well, I've imported the data from access database, but I don't know how to do the AutoNumber in MS SQL,
Also I don't know how can I add that Random thing *See the arrow in the screenshot*
Thanks in advance
How can I have display random value for field varchar(10).
View 5 Replies View RelatedI once came across a database in which when ever a row was inserted, a unique random code was also inserted in a column. This code generated was similar to format like {8A5FE5F1-6BDA-476C-A4DB584A7710FBF91121054625}. I was told that SQL2000 has some inbuilt functionality for this.
Any idea on how to do this using SQL2000 or coding.
I feel this is much better than Identity Column as using autonumbers in quesrystring is too much risky as they easily readable.
Please help.
Thanks
I have a table with 3 fields SiteID (PK), SiteCode, SiteName.
For each SiteCode I have several rows with different SiteName.
For example: 1 327 a
2 327 b
3 327 c
4 328 aa
5 328 bb......
I need to retrieve each time SiteCode with random SiteName.
For example: 327 b , 327 a , 327 c
328 aa 328 bb 328 aa.
I need it very urgent, so any ideas will be very helpful. Thanks to all,
How Can I select a Random set of records from a database table?
View 3 Replies View RelatedI have a customer that wants to be able to generate a random list...
-----------------------
The list is case numbers, and I can get those easy enough by saying
SELECT
dbo.table_case.id_number
FROM
dbo.table_case
Now, what I want to do is take that list and extract 1 out of every 1000, picking out random id_numbers to display.
Is this something I can do via a SQL query?
Thanks,
Dan
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 Experts ,
I want to insert datas randomly to all columns of a table.The columns have different datatypes like int,varchar,datetime. What to do??? It is urgent so please help.
TIA
RKNAIR
Hi Experts ,
I want to insert datas randomly to all columns of a table.The columns have different datatypes like int,varchar,datetime. How to create a procedure for the same??? please help.
TIA
RKNAIR