SQL Union Problems When Trying to Retrieve Random Records

[Jeremy]

I have a situation where i am trying to run two query's and retrieve
one record from each query, then using Union combine them into one
recordset. The First Query is strait forward and should just return
the matching record however the second query needs to find a random
record and return it. The union is causing me some trouble. It seems
that any method i try to find a random record just causes an error.

Here is an example of a query that's causing the problem.
--------------------------------------------------------------------------------------------------------

declare @variable1 smallint
declare @variable2 int

set @variable1 = 10002
set @variable2 = 1001211720

select col1, col2, col3, col4, col5, col6
from table1
where col1 = @variable1 and col2 = @variable2

union

select col1, col2, col3, col4, col5, col6
from table2
where col2 = @variable2
order by newID()

--------------------------------------------------------------------------------------------------------
I have also tried the following which does not provide an error but i
cannot find a way only to return one record from the second query
--------------------------------------------------------------------------------------------------------

declare @variable1 smallint
declare @variable2 int

set @variable1 = 10002
set @variable2 = 1001211720

select newID() as colID, col1, col2, col3, col4, col5, col6
from table1
where col1 = @variable1 and col2 = @variable2

union

select newID() as colID, col1, col2, col3, col4, col5, col6
from table2
where col2 = @variable2
order by colID

If anyone has any ideas, fixes, or thoughts i would appreciate them...
thanks in advance...
FYI i am fairly inexperienced in SQL so please feel free to let me know
if you need more of an explination.

[Alexander Kuznetsov]

Jeremy,

look up SELECT TOP 1 in Books Online

[Helmut Woess]

Am 6 Jun 2006 08:02:01 -0700 schrieb Jeremy:
....

I have also tried the following which does not provide an error but i
cannot find a way only to return one record from the second query

Without looking at the rest of your query only a hint for getting only one
record (works only for SQL2000 and higher):

select col1, col2, ... from table where ...
union
select * from
(select top 1 col1, col2, col3, ... from table where .... order by col1,
....) as X

.... and you get only the first record from the second part.

bye, Helmut

[JMoore2026]

i found that this does not get a random value... it only returns the
first record... how would i use this and return a random value.
thanks
jeremy
Alexander Kuznetsov wrote:

Jeremy,

look up SELECT TOP 1 in Books Online

[Erland Sommarskog]

JM********@gmail.com (JM********@gmail.com) writes:

i found that this does not get a random value... it only returns the
first record... how would i use this and return a random value.

select col1, col2, col3, col4, col5, col6
from table1
where col1 = @variable1 and col2 = @variable2
union
select col1, col2, col3, col4, col5, col6
from (SELECT TOP 1 col1, col2, col3, col4, col5, col6
from table2
where col2 = @variable2
order by newID()) AS d

You need a derived table into which you can put the TOP 1 and ORDER BY.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Alexander Kuznetsov]

>

select col1, col2, col3, col4, col5, col6
from (SELECT TOP 1 col1, col2, col3, col4, col5, col6
from table2
where col2 = @variable2
order by newID()) AS d

Erland,

My server has crashed and I cannot verify it right now, but I have a
feeling that newID() will be calculated for every row, which may be
inefficient. I was thinking of a more efficient way to get a random
row. Suppose there is an identity column, then you could
do something like this to get a random identity:

select min(id) from table2,
(select max(id) maxID, min(ID) minID from table2) t
where table2.id > t.minID + (t.maxID - t.minID)*rand()

What do you think?

[Erland Sommarskog]

Alexander Kuznetsov (AK************@hotmail.COM) writes:

My server has crashed and I cannot verify it right now, but I have a
feeling that newID() will be calculated for every row, which may be
inefficient. I was thinking of a more efficient way to get a random
row. Suppose there is an identity column, then you could
do something like this to get a random identity:

select min(id) from table2,
(select max(id) maxID, min(ID) minID from table2) t
where table2.id > t.minID + (t.maxID - t.minID)*rand()

What do you think?

That presumes that the values in the id column are decently evenly
distributed. If there are occasional large gaps, the distribution
will be skewed.

Yes, ORDER BY newid() + TOP 1 will read all rows, which for a large
table can be costly.

In SQL 2005 there is TABLESAMPLE, but apparently it's not that random
for smaller tables.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[JMoore2026]

thanks guys... i got it to work using this method. This table should
not end up being very large and i'm not to worried about the cost of
this method... If anyone has a more efficient and presice method though
let me know...
Thanks
Jeremy
Jm********@gmail.com
Erland Sommarskog wrote:

JM********@gmail.com (JM********@gmail.com) writes:

i found that this does not get a random value... it only returns the
first record... how would i use this and return a random value.

select col1, col2, col3, col4, col5, col6
from table1
where col1 = @variable1 and col2 = @variable2
union
select col1, col2, col3, col4, col5, col6
from (SELECT TOP 1 col1, col2, col3, col4, col5, col6
from table2
where col2 = @variable2
order by newID()) AS d

You need a derived table into which you can put the TOP 1 and ORDER BY.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Greg D. Moore \(Strider\)]

"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn**********************@127.0.0.1...

Alexander Kuznetsov (AK************@hotmail.COM) writes:

My server has crashed and I cannot verify it right now, but I have a
feeling that newID() will be calculated for every row, which may be
inefficient. I was thinking of a more efficient way to get a random
row. Suppose there is an identity column, then you could
do something like this to get a random identity:

select min(id) from table2,
(select max(id) maxID, min(ID) minID from table2) t
where table2.id > t.minID + (t.maxID - t.minID)*rand()

What do you think?
That presumes that the values in the id column are decently evenly
distributed. If there are occasional large gaps, the distribution
will be skewed.

Yes, ORDER BY newid() + TOP 1 will read all rows, which for a large
table can be costly.

Not sure how large the table Alexander is looking at, but this is basically
what we're doing to retrieve a random ad.

Works well for us and was faster than our old method.

But we're generally selecting from a temple table of less than 100 records.

In SQL 2005 there is TABLESAMPLE, but apparently it's not that random
for smaller tables.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Greg D. Moore \(Strider\)]

<JM********@gmail.com> wrote in message
news:11**********************@h76g2000cwa.googlegr oups.com...

thanks guys... i got it to work using this method. This table should
not end up being very large and i'm not to worried about the cost of
this method... If anyone has a more efficient and presice method though
let me know...
I don't, but one I've wanted to try in SQL 2005 (but haven't had time) is to
use ROW_NUMBER to number my results, get a max count and then use the random
function to select from that range.
Thanks
Jeremy
Jm********@gmail.com
Erland Sommarskog wrote:

JM********@gmail.com (JM********@gmail.com) writes:

i found that this does not get a random value... it only returns the
first record... how would i use this and return a random value.

select col1, col2, col3, col4, col5, col6
from table1
where col1 = @variable1 and col2 = @variable2
union
select col1, col2, col3, col4, col5, col6
from (SELECT TOP 1 col1, col2, col3, col4, col5, col6
from table2
where col2 = @variable2
order by newID()) AS d

You need a derived table into which you can put the TOP 1 and ORDER BY.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx