Rows without repeating records

Question

id     | name 
-------+------------------------------------- 
209096 | Pharmacy 
204200 | Eyecare Center 
185718 | Duffy PC 
214519 | Shopko 
162225 | Edward Jones 
7609   | Back In Action Chiropractic Center

I use select id, name from customer order by random()

There are 6 records i just want that when ever i query, i will get a unique row each time for six times and then it starts again from first or the records are ordered each time that the top one did't repeat

Answer 1

This will give you 6 random rows each time. The Group By is to ensure unique rows if your id is not a unique primary key, so maybe not needed - depending on your table structure.

SELECT TOP 6 id, name, ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) AS [RandomNumber]
FROM customer
GROUP BY id,name
ORDER BY [RandomNumber]

Edit: Sorry! Didn't read the question properly. Although you can use this to get a random row each time :)

If you want to specifically get all 6 rows in a random order 1 at a time you will need to store the order somewhere. Suggest creating a temp table and selecting from there, or if you are using a front end webpage get all 6 rows and store in a dataset.

Answer 2

You can use that logic, "milisecond" part of current date is always changing. We have a id column as numeric. So we can use modular function to get randomized order:

create table #data(id numeric(10), name varchar(20))
insert #data select 209096 , 'Pharmacy'
insert #data select 204200 , 'Eyecare Center'
insert #data select 185718 , 'Duffy PC'
insert #data select 214519 , 'Shopko'
insert #data select 162225 , 'Edward Jones'
insert #data select 7609   , 'Back In Action Chiropractic Center'

select * from #data order by id % (datepart(ms, getdate()))

Answer 3

OK Maybe there is another way to do it just in SQL. Add a new BIT column "selected". Definitely not the fastest/best performance way to do it.

DECLARE @id INT
IF NOT EXISTS (SELECT TOP 1 id FROM customer WHERE selected = 0)
BEGIN
   UPDATE customer SET selected = 0
END

SELECT @id = id FROM
(SELECT TOP 1 id, ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) AS [RandomNumber]
FROM customer WHERE selected = 0
ORDER BY [RandomNumber]) a

UPDATE customer SET selected = 1 WHERE id = @id
SELECT id, name FROM customer WHERE id = @id

Answer 4

Isnt the question similar to one of your other questions: http://stackoverflow.com/q/3633040/437675 with a possible answer? If not, could you please clarify the difference?