Return Random Rows
May 6, 2006Are there any way to execute a procedure and return N random rows?
ADVERTISEMENT
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
SQL 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?
Random Rows In Respect To Their Priorities
Sep 28, 2007I 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.
How Can Select Random Rows From Database?
Jan 23, 2008Hi
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();
}
}
SQL Server Random Rows Returned
Sep 23, 2004As 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?
Distinct Random Rows Using NewID()
May 26, 2008I 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
Flat File With Random Bad Rows.
Aug 13, 2007I 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.
Transact SQL :: Getting Random Rows From Table / Two From Each Group
Sep 2, 2015I 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?
Rows Skipped Out In Stored Procedure While Return All Rows If Query Executed Seprate
Nov 8, 2007Hi All,
I am using sql server 2005. I stuck out in a strange problem.
I am using view in my stored procedure, when I run the stored procedure some of the rows get skipped out means if select query have to return 10 rows then it is returning 5 rows or any other but not all, also the records displyaing is randomly coming, some time it is displaying reords 12345 next time 5678, other time 2468.
But if I run seperately the querys written in SP then it returns all the rows. Please give me solution why it is happening like this.
There are indexes in the tables.
Once I shrink the database and rebuild the indexes, from then this problem is happening. I have rebuild the indexes several time, also updated the statistics but nothing improving.
But nothing is improving
Return Two Rows From One Rows Data
Jul 20, 2005I know this table is designed wrong for what I am doing but I hope Ican do it. I have a table like this.Prod_A_Jan, Prod_A_Feb, Prod_B_Jan, Prod_B_FebI want a query that returns data like this (two rows of data)"ProdA", Prod_A_Jan, Prod_A_Feb"ProdB", Prod_B_Jan, Prod_B_FebI know two queries can get it but I want one. Any Help would begreat!!!Sheila T.
View 3 Replies View RelatedReturning 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 RelatedNeed Help In Return How Many Rows
Dec 14, 2004HI All,
I need help in sp. I have the sp that return the result but then i also want count how many rows are the result. Does anyone know how to do that?
This is my sp, and when it returns the data i also want it to return the how many rows are the result.
CREATE procedure dbo.ph_getphonebookoption
@country nvarchar(100),
@company nvarchar(100),
@office nvarchar(100)
as
select
Ext,
FName,
LName,
Title
from
phonebook
where country =@country
and company=@company
and office =@office
select count (*)
GO
How To Return A Specified # Of Rows
Oct 29, 1999Trying to do a paging scheme without using #Temp tables in MS SQL 7.0
Client calls a sp passing 1 and sql returns the first 100 records.
Client sends a sp passing 100 and gets the next 100 records.
Process continues till @@fetch_status <> 0 or the client can stop sending requests.
I implemented it easily using fetch absolute into a #temp table but this has dissaster potential in a multiuser environment since everyone will be using this query continously and there is no user limit.
What other options do I have?
Thanks,
John
O Rows Return
Feb 19, 2008is there is any way to find out whether my query return 0 rows inside a stored procedure.
View 2 Replies View RelatedReturn Rows Other Than Top 10
Oct 5, 2007Hi All,
I have a question,
Select top 10 * from employee
the above statement return top 10 row.
but i want the rows from the table other than the top 10. Can any one help me to get it.. iam using SQL server 2005
Thanks for the help
Thanks
Bhaskar
Sql Help With Number Of Rows Return Value
May 14, 2008hi,i have a stored procedure like this in SQL server ,it returns proper value if data is there for a given id.But if there is no data,it returns row/rows of NULL value and that is counted towards "number of row returned"..Shouldn't it be like,if there are null values in a row,that row should not be counted towards rows returned value .?Rightnow if no value returned from either of the select,it still returns as 2 rows instead of 0 rows.How do handle this situation in SQL? thanks for your help
SELECT SUM(col1) AS SUM_COL1, SUM(col2) AS SUM_COL2, SUM(col3) AS SUM_COL3, SUM(col4) AS SUM_COL4FROM TABLE1WHERE (ID = nn) UNION all
SELECT SUM(col22) AS SUM_COL22 ,cast(null as int) as c1,cast(null as int)as c2,cast(null as int) as c3FROM table2WHERE TABLE2 = nn)
Exec An SP To Return Rows To Another SP
Apr 8, 2004Hi,
Any idea how to write a (T-)SQL Stored Procedure which uses a SubQuery calling
a SELECT query from another SP??
Something like...
CREATE procedure spThisSP(@param varchar(20))
AS
SELECT theColumn FROM theTable WHERE theColumn NOT IN (EXEC spAnotherSP @param)
Cheers
What Is Better? Return All Rows Or Nested TOP X?
Feb 24, 2006I have a database that has 100,000+ records in a table. Am I better off returning all of the records from a search even if it is 50,000 records or is it better to do a SELECT COUNT(*) And nested SELECT TOP x statements to only return 1 page of results? What is the best practice for a situation like this?
SELECT * FROM myTable LEFT OUTER JOIN ... LEFT OUTER JOIN......WHERE something=@something AND another.... AND...
OR
SELECT COUNT(*) FROM myTable LEFT OUTER JOIN ... LEFT OUTER JOIN......WHERE something=@something AND another... AND... ORDER BY @OrderAnd
SELECT TOP(@pagesize) FROM (SELECT TOP(@pagesize * @pagenum) FROM myTable LEFT OUTER JOIN ... LEFT OUTER JOIN......WHERE something=@something AND another... AND... ORDER BY @Order)ORDER BY @revOrder
How To Return Rows 200 - 300 Of A 500 Row Result Set
Sep 6, 2000What is the easiest way to return rows 200 through to 300 of a 500 row result set using SQL? Is there a simple way of doing this or do I need to write some Transact SQL? Any ideas would be appreciated.
Thanks
Rod
How Many Rows Will A Query Return?
Apr 8, 2006Does sql server have a mechanism (aside from count()) that for any given SELECT query will tell you only how many rows it will return without actually returning the data?
The reason for this is that we have a generic lookup form in an application that is used on almost every screen (we have a lot of screens, so it gets a lot of different, sometimes complicted, queries passed to it to use for the lookup, and having to manually edit the query to use count over all the select clauses doesn't seem like the best way to handle this. If we could do a kind of 'trial run' against the server just to get the number of rows and use that to help set up the form, that would be ideal.
Return A Range Of Rows?
Apr 12, 2007Hi.
How does one return a range of rows.
I know that "Top 5" will return rows 0 - 5
but, how do I get 6 - 10?
thanks
Return Count Instead Of Rows?
Oct 23, 2013The following query returns 2142 rows which is correct.
Code:
select COUNT(*), sum(v.currentMarket)
from TRMaster m
inner join TRValue v on v.Year = m.Year and v.Parcel = m.Parcel
inner join TRProp p on P.PropCode = V.PropCode and p.PropType = 'A'
where m.Year = 2013 and m.deleted = 0 and m.ReviewDateTime is null and m.Status = 1
group by m.Year, m.Parcel
having SUM(v.currentmarket) > 0
How can I convert this query so that it returns just the count of 2142?
Return Certain Number Of Rows
Apr 17, 2008I have a Dataset that I am populating from a SQL Query. I am then using the dataset to populate a report in Reporting Services. What I want to do is return a standard number of rows in my dataset. (Let's say 10.) Even if my query does not have any rows in it, I want 10 empty rows returned to the dataset. If my query has 7 rows in it then I want to add on 3 empty rows and return it. I will not have more than the standard number of rows.
I cannot get the table in the report to show up if the dataset is empty, but still want the table to display with 10 empty rows. I have searched how to do this online but am getting nowhere. (I know how to add one empty row but not a set number.
Return Tables With 0 Rows
Nov 20, 2006Hello All
Please can anyone advice me how I can fetch list of all tables which have no records in it in sql server
Thanks
How To Return A Range Of Rows??
Apr 12, 2007Hi.How does one return a range of rows.I know that "Top 5" will return rows 0 - 5but, how do I get 6 - 10?thanks
View 17 Replies View RelatedHow To Return A Range Of Rows?
Jul 20, 2005How can a SQL statement be written to return a specified range ofrows? For example:-- tblContact-- (-- SSN char(9),-- FirstName varchar(50),-- LastName varchar(50)-- )-- This table contains 500 rows.Select * from tblContact -- Return only rows 5 through 10Thanks
View 2 Replies View RelatedURGENT - Random 10 From Random 20 (2 Tbls)
Oct 14, 2004I'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.
How Do I Configure An SqlDataSource To Only Return The Top N Rows?
Aug 1, 2006I have a charting control that is fed from an SqlDataSource. How do I set the SqlDataSource to only return the top N records where N is set by a web form control?The user should have the option to chart the top 3, 5, or 10 items.Is the best approach to switch to an ObjectDataSource?
View 3 Replies View RelatedReturn Only The Rows From A List That Has The Same Value From One Field
Aug 2, 2006I am in need of help to develop a query
I have two tables Exams and Exams_lab, that are joined by a field id_exame. I want to return The Exams that has all the dependent rows in Exames_lab with the same value in the status_int field of Exames_lab. Can anyone Help Me?
Lets see an example
tb_exame
id_exame
date_exame
1
07/01/2006
2
08/01/2006
3
09/01/2006
tb_exame_lab
id_exame_lab
id_exame
desc_exame
status_exame
1
1
NORMAL
1
2
1
0
3
2
NORMAL
1
4
2
PROBLEMS
1
5
2
OK
1
6
3
OK
0
in this exemple my query must return only id_exame 2 and 3 because id_exame 1 has two different values on id_status on tb_exame_lab
can anyone help me?
Help With Select To Return Duplicate Rows
Mar 30, 2007Can someone look at this and tell me where I went wrong? I'm trying to return all duplicate rows that have the same lastName and Address. It returns rows but they don't look like dups.SELECT TOP (100) PERCENT dbo.tblClient.LastName, dbo.tblClientAddresses.Address
FROM dbo.tblClient INNER JOIN
dbo.tblClientAddresses ON dbo.tblClient.Client_ID = dbo.tblClientAddresses.Client_ID
GROUP BY dbo.tblClient.LastName, dbo.tblClientAddresses.Address
HAVING (COUNT(dbo.tblClientAddresses.Address) > 1)
ORDER BY dbo.tblClientAddresses.Address
Return Number Of Rows From A Table
Dec 9, 2007Im am trying to return the number of rows in a table and i only can get a response of true thanks for any help
View 3 Replies View RelatedHow To Return No Rows If A Variable Is NULL
Apr 1, 2008Hello,
I have a stored procedure that accepts a number of different input parameters that populate some variables in my stored procedure.
I want to have this stored procedure return nothing if some of these variables aren't filled out (they are populated by a search page the user fills out).
I'm not very familiar with writing stored procedures, so any help you can give me is appreciated.
Thanks!