how to retrieve random records from table

[pritisarode]

Hi,
I m doing slideshow application. fro that i m using Ms Sql 7.0 server.
Now i want to retrieve each time 10 different records order by date
(it is the column) from table out of 100 records.
Plz, help me for writing the sql query for this.

[shoxdude]

Hi,
I m doing slideshow application. fro that i m using Ms Sql 7.0 server.
Now i want to retrieve each time 10 different records order by date
(it is the column) from table out of 100 records.
Plz, help me for writing the sql query for this.

select top 10 * from table order by newid(), mydate

[codemobile]

Yes this is correct.

select top 10 * from table order by newid(), orderdate

[Delerna]

Maybe this is what you mean

 
SELECT TOP 10 a.Field1,a.Field2,a.Field3
FROM
(   SELECT RAND( (DATEPART(mm, GETDATE()) * 100000 )
           + (DATEPART(ss, GETDATE()) * 1000 )
           + DATEPART(ms, GETDATE()) )*100000 as RowPicker,
           YourTable.Field1,YourTable.Field2,YourTable.Field3
   FROM YourTable
)a
ORDER BY RowPicker
 

check out RAND in the help document