I am assuming that you have some additional condition in your select statement such as a TOP (N) clause - otherwise each select would return all the rows.
To exclude previously selected rows, you would need some way of keeping track of what was selected. Also, when you get to near the end, if the requested number of rows are not returned then you would need to reuse the ones that were already used etc.
In any case, here is a partial solution. I am using a second table to store what was already consumed. This can be made simpler if you have a PK in your table. Also, it is not perfect because when you don't have enough unused rows it does not return as much as requested.CREATE TABLE #B(id INT, id2 INT);
CREATE TABLE #A(id INT, id2 INT);
INSERT INTO #A VALUES (1,2),(3,4),(5,6),(7,8),(9,10);
---------------------------------------------------
DECLARE @rowsRequested INT;
SET @rowsRequested = 2;
INSERT INTO #b
OUTPUT INSERTED.*
SELECT TOP (@rowsRequested) * FROM
(
SELECT * FROM #A
EXCEPT SELECT * FROM #B
)s
ORDER BY NEWID();
IF (@@ROWCOUNT < @rowsRequested)
BEGIN
TRUNCATE TABLE #b;
END
---------------------------------------------------
DROP TABLE #A;
DROP TABLE #B;
select random rows
Posted - 08/02/2012 : 06:13:36
