Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Random select 5000 records 2

Status
Not open for further replies.

devon59

Technical User
Nov 30, 2005
18
US
Hi there,
What is the syntax to do random select 5000 records?
Could any one please help. Thank you very much.
 
Thank you.

However, one more question regarding this. When I UNION this query to another one, I got an error message saying "Error Near Order". Could you please help? Thank you.


(SELECT *
FROM mail
left outer join Orders
on mail.ID=OrdersID
WHERE
and Orders IS NOT NULL
)
UNION
(SELECT top 5000 *
FROM mail
left outer join Orders
on mail.ID=OrdersID
WHERE
and Orders IS NULL
order by newid()
)
 
try this
Code:
SELECT *
    FROM mail
    left outer join Orders
    on mail.ID=OrdersID
    WHERE Orders IS NOT NULL
      
UNION
select x.* from
    (SELECT top 5000 *
    FROM mail 
    left outer join Orders
    on mail.ID=OrdersID
    WHERE Orders IS NULL
    order by newid() 
     ) x

Denis The SQL Menace
SQL blog:
Personal Blog:
 
Hi,
I got another error. I basically did not do select all variables(*) in the second query of the union so the error message said

"ORDER BY items must appear in the select list if the statement contains a UNION operator."

Basically, this is what I have.

SELECT A, B, C
FROM mail
left outer join Orders
on mail.ID=OrdersID
WHERE Orders IS NOT NULL

UNION
select x.* from
(SELECT top 5000 A,B,C
FROM mail
left outer join Orders
on mail.ID=OrdersID
WHERE Orders IS NULL
order by newid()
) x
 
I can't test it so try this, should work

SELECT A, B, C
FROM mail
left outer join Orders
on mail.ID=OrdersID
WHERE Orders IS NOT NULL

UNION
select x.a,x.b,x.c from
(SELECT top 5000 A,B,C,newid() as blah
FROM mail
left outer join Orders
on mail.ID=OrdersID
WHERE Orders IS NULL
order by newid()
) x

Denis The SQL Menace
SQL blog:
Personal Blog:
 
Thank you thank you thank you! It worked!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top