Re: random selection

From: Aaron Bertrand - MVP (aaron_at_TRASHaspfaq.com)
Date: 05/26/04 

Date: Wed, 26 May 2004 17:17:12 -0400

> However, I like to be able to select random number of
> rows - not from the top or bottom.

You want a random number of rows, or 10 rows in any order?

I'll assume the latter:

ORDER BY NEWID()

(See http://www.aspfaq.com/2132)

> Also, on which columns do you suggest
> I create indexes? Thank you.

Well if you're going to be running queries against a DATETIME /
SMALLDATETIME column, that is usually a no-brainer. In fact, most of my
database applications have multiple tables with clustered indexes on the
[small]datetime columns, because that is such a heavy query requirement.

A couple of other suggestions:

You might want to avoid BETWEEN for date range queries, as the results can
often be ambiguous. In this case, if your posteddate has time information,
your query will include rows posted on 20040228 at midnight, but not at
12:01 am. See http://www.aspfaq.com/2280 for more info.

I'm also surprised that you're joining on posteddate... if this is really
necessary, I hope that you are merely storing the date, because if this
includes time information, you are storing redundant data. The join should
only be on sequence or seqno, if this is a primary/foreign key relationship.
(Which reminds me to suggest that you keep column names the same... figuring
out that seqno and sequence have the same meaning would drive me batty!) 

-- 
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Ashley Miramar" <anonymous@discussions.microsoft.com> wrote in message
news:12e2d01c44364$c8b433a0$a401280a@phx.gbl...
> Friends, I have written the following script to select the
> top 10 rows of this table.
>
> Select top 10 t.CASE, t.AMOUNT, t.DLASTNAME, t.DFIRSTNAME,
> t.DMIDNAME,
> t.DSUFFIX, t.FILINGTYPE, t.JUDGMENTDATE, t.PLASTNAME,
> t.PFIRSTNAME,
> t.PMIDNAME from tablecates t (nolock) inner join eRecords
> e (nolock)
> on t.sequence = e.SeqNo and t.Posteddate = e.Posteddate
> where t.type = 'UMM' and t.ResearchNo = '12565555' and
> t.posteddate
> between '20040101' and '20040228'
>
> However, I like to be able to select random number of
> rows - not from the top or bottom.  Can you please show me
> what script or store procedure will do this?   I am so new
> in writing scripts.  Also, on which columns do you suggest
> I create indexes?  Thank you.
>
> Ashley
>

Relevant Pages

  • Re: match machine name to user name
    ... :>: query the server from a client for a username based on a machine name. ... :> It reads better if you post inline or at the bottom. ... Is it better to use the script to poll the server or to ...
    (microsoft.public.scripting.vbscript)
  • Re: match machine name to user name
    ... > dim strComputer, oConn, colComputer, oComputer, oLocator, sUser, sPass ... > set oArgs = wscript.arguments ... I will be running the script from my machine. ... so I guess I am used to reading 'bottom up'. ...
    (microsoft.public.scripting.vbscript)
  • Re: chopping off the last few lines.
    ... > various pre-processing actions upon it via a ksh script. ... bottom of a file. ... ## The bufarray is a rotating buffer which contains the last N lines ...
    (comp.unix.shell)
  • Re: Drawing origin
    ... manual intervention for each drawing. ... What defines the 'bottom left of each drawing'? ... script on several files. ... I am using "Batch Script Processor v3.0.11" as a batch processor and I ...
    (comp.cad.autocad)
Loading