Random select 5000 records 2

[devon59]

Hi there,
What is the syntax to do random select 5000 records?
Could any one please help. Thank you very much.

 

[SQLDenis]

select top 5000 *
from table
order by newid()

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[devon59]

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

 

[SQLDenis]

try this

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:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[devon59]

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

 

[SQLDenis]

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:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[devon59]

Thank you thank you thank you! It worked!