Rows without repeating records
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
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.
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()))
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
精彩评论