Re: random selection
From: Ashley Miramar (anonymous_at_discussions.microsoft.com)
Date: 05/26/04
- Next message: sbjennifer: "Re: SQL server agent service & SQL server service fail to start"
- Previous message: Aaron Bertrand - MVP: "Re: Updating an entire table"
- In reply to: Aaron Bertrand - MVP: "Re: random selection"
- Next in thread: Aaron Bertrand - MVP: "Re: random selection"
- Reply: Aaron Bertrand - MVP: "Re: random selection"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 26 May 2004 15:37:33 -0700
Aaron,
Thank you so much for your suggestions. I will take that
into consideration when writing the querry. As far as
what type of search I want, I like to get a random number
of rows without any order. Please see if you can send me
the query. Thanks.
Ashley.
>-----Original Message-----
>> 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
>>
>
>
>.
>
- Next message: sbjennifer: "Re: SQL server agent service & SQL server service fail to start"
- Previous message: Aaron Bertrand - MVP: "Re: Updating an entire table"
- In reply to: Aaron Bertrand - MVP: "Re: random selection"
- Next in thread: Aaron Bertrand - MVP: "Re: random selection"
- Reply: Aaron Bertrand - MVP: "Re: random selection"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
