Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to extract random record from Sql Server table

Posted on 2008-06-12
14
Medium Priority
?
511 Views
Last Modified: 2010-04-21
Greetings all

I have a Sql Server table containing various quotes. I would like to extract a random quote each time a particular page is loaded.

How do I get a random record out?


In advance, thanks!!

  allanmark
0
Comment
Question by:allanmark
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
14 Comments
 
LVL 8

Expert Comment

by:matrix_aash
ID: 21767650
select * from tablename where quotecolumnname = 'random quote name'

hope this helps

Cheers.
0
 

Author Comment

by:allanmark
ID: 21767776
Hi!

Did the following: select * from Quotes where Q_Quote = 'random Q_Quote'

Executes, but displays nothing. What have I missed?
0
 
LVL 8

Expert Comment

by:matrix_aash
ID: 21767796
Is there a value with 'random q_quote in the column q_quote.

What I meant was just an example by random quote name.


You have put the value which you want to retrive.

Hope this make sense.

If possible send me few rows with the columns name and the data also and what you want to retrive and i will try an answer your question more precisely.

Cheers
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

Author Comment

by:allanmark
ID: 21767849
Thanks!

Snippet1 = table structure; snippet 2 = data loaded into table.

What I want is to extract all the fields, on a purely random basis.
SNIPPET 1:
 
CREATE TABLE [dbo].[Quotes]
(
Q_Id			int		identity(1, 1)  not null,
Q_Quotes		varchar(400)	not null,
Q_Author		varchar(30)not null,
	CONSTRAINT [PK_Quotes] PRIMARY KEY CLUSTERED
	([Q_Id] ASC)
)
 
 
SNIPPET 2:
 
insert into Quotes (Q_Quote, Q_Author)
values ('There is nothing to writing.  All you do is sit down at a typewriter and open a vein.', 'Walter Wellesley Smith')
 
insert into Quotes (Q_Quote, Q_Author)
values ('You must stay drunk on writing so reality cannot destroy you.', 'Ray Bradbury')
 
insert into Quotes (Q_Quote, Q_Author)
values ('So often is the virgin sheet of paper more real than what one has to say, and so often one regrets having marred it.', 'Harold Acton')
 
insert into Quotes (Q_Quote, Q_Author)
values ('The role of a writer is not to say what we all can say, but what we are unable to say.', 'Anaïs Nin')

Open in new window

0
 
LVL 8

Expert Comment

by:matrix_aash
ID: 21767871
SELECT * FROM QUOTES

THE ABOVE QUERY WILL GIVE YOU EVERYTHING.

CHEERS.
0
 

Author Comment

by:allanmark
ID: 21767886
Sorry .... I'm not doing very well explaining :(  :(

What I meant was that I want to extract fields  Q_Id, Q_Quotes, Q_Author  and have a random extraction each time. Hope this makes more sense.
0
 
LVL 8

Expert Comment

by:matrix_aash
ID: 21767909
what do you mean by random extraction ?
0
 

Author Comment

by:allanmark
ID: 21767944
In other wards, each time that I execute the statement, I should get back a different record.
0
 
LVL 8

Expert Comment

by:matrix_aash
ID: 21767961
Are you going to adding data to this table on daily basis or can u tell me the intervals ?
0
 

Author Comment

by:allanmark
ID: 21768019
Data will most likely be added once a week and by a single user, wiuth appropriate porivileges.
0
 
LVL 8

Accepted Solution

by:
matrix_aash earned 800 total points
ID: 21768035
SELECT TOP 1 * FROM Q_QUOTES
0
 
LVL 8

Expert Comment

by:matrix_aash
ID: 21768041
AFTER THE DATA IS INSERTED THE ABOVE QUERY WILL ALWAYS GIVE YOU DIFFERENT RESULTS.
0
 
LVL 21

Assisted Solution

by:naspinski
naspinski earned 600 total points
ID: 21768104
Here is 2 different methods, one with LINQ, and one with the older style sql connections.  As you can see the LINQ is much cleaner.  Also, if your quote pool was a static size, you could cut out the call that gets the maxID size.  This is assuming you have a constant 1-MAX integer ID to reference.
// declare your random
Random rand = new Random();
 
// with LINQ
itemsDataContext db = new itemsDataContext(); // call your dbml
int maxId = (from q in db.items select q.itemID).Max() + 1; // get your max ID
string quoteWithLinq = (from p in db.items where p.itemID==(rand.Next(1, maxId )) select p).First().quote;
 
// or with SqlClient
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["mytestDB"].ConnectionString);
SqlCommand sql = new SqlCommand("SELECT MAX(itemID) FROM items", con);
string quoteWithSqlClient = string.Empty;
con.Open();
    int maxID = (int)sql.ExecuteScalar(); // get yoru max ID
    SqlCommand sql2 = new SqlCommand("SELECT quote FROM items WHERE itemID = " + rand.Next(1, maxID + 1).ToString(), con);
    quoteWithSqlClient = sql2.ExecuteScalar().ToString();
con.Close();
 
// proof that they work
Response.Write("quoteWithLinq = " + quoteWithLinq + "<br />");
Response.Write("quoteWithSqlClient = " + quoteWithSqlClient);

Open in new window

0
 

Author Closing Comment

by:allanmark
ID: 31466480
Excellent answers!! Many thanks!!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question