random selection  
Author Message
Wynston





PostPosted: Wed May 26 16:17:12 CDT 2004 Top

SQL Server >> random selection

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

SQL Server185  
 
 
Aaron





PostPosted: Wed May 26 16:17:12 CDT 2004 Top

SQL Server >> random selection > 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/






> 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
>


 
 
Ashley





PostPosted: Wed May 26 17:37:33 CDT 2004 Top

SQL Server >> random selection 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/
>
>
>
>



>> 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
>>
>
>
>.
>
 
 
Aaron





PostPosted: Thu May 27 07:36:28 CDT 2004 Top

SQL Server >> random selection Not sure of the business purpose of this, but this will return anywhere from
1 - 20 rows, and order them randomly.





SELECT columns FROM table ORDER BY NEWID()
SET ROWCOUNT 0

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/






> 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/
> >
> >
> >
> >



> >> 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
> >>
> >
> >
> >.
> >


 
 
Ashley





PostPosted: Thu May 27 10:26:47 CDT 2004 Top

SQL Server >> random selection Thank you so much.

Ashley


>-----Original Message-----
>Not sure of the business purpose of this, but this will
return anywhere from
>1 - 20 rows, and order them randomly.
>




>SELECT columns FROM table ORDER BY NEWID()
>SET ROWCOUNT 0
>
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>
>



>> 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/
>> >
>> >
>> >
>> >



>> >> 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
>> >>
>> >
>> >
>> >.
>> >
>
>
>.
>