SELECT DISTINCT random columns limited by a number

Question

I have a very specific question regarding using SELECT DISTINCT...INTO...

I want to select exactly 1000 random rows into a new table using SELECT DISTINCT which forms combinations of rows from my previous table. For example, my current query is:

SELECT DISTINCT p1.id AS id1, p2.id AS id2 INTO my_new_table FROM oldtable AS p1, oldtable AS p2;

How do I modify this so that I am selecting at most 1000 random rows? I'm dealing with millions of records so a solution that is efficient is obviously desired.

Where do you put the above command into my command? Putting it at the end selects 1000 random rows from oldtable but I want to select random rows in the combinations of rows. — Quanquan Liu, Feb 06 '15 at 17:34
`order by random()` is not the best option for large tables -- if that is your interest, see http://stackoverflow.com/questions/8674718/best-way-to-select-random-rows-postgresql — pozs, Feb 07 '15 at 00:30

score 0 · Answer 1 · answered Feb 06 '15 at 17:26

0

This is how I would go about selecting random 1000 rows quickly:

SELECT TOP 1000
    FROM OldTable as OT
    WHERE ...
    ORDER BY NEWID()

answered Feb 06 '15 at 17:26

IKnowledge

221
3
12

SELECT DISTINCT random columns limited by a number

1 Answers1