0

Possible Duplicate:
SQL Server Random Sort

I guess it sounds stupid but I want to retrieve a few records randomly each time user refreshes the page. is there any Query available for SQL Server 2005 to do that or should I do this in page?

Community
  • 1
  • 1
ePezhman
  • 4,010
  • 7
  • 44
  • 80

2 Answers2

1

Try this:

SELECT TOP 10 Field1, ..., FieldN FROM Table1 ORDER BY NEWID()

NEWID() creates a unique value of uniqueidentifier type. Take a look at this.

Hope it helps.

Sandy
  • 11,332
  • 27
  • 76
  • 122
-2

Yes, there is

I don't know about SQL Server but seeing as SQL is meant to be standardised, you could try the MySQL way: As far as I can remember you just have to use a MySQL function, rand() to sort your SELECT call and then limit the length to 1, so as to get only the first result.

SELECT * FROM tableName ORDER BY rand() LIMIT 1

Give that a go, hope it helps!

jt78
  • 906
  • 2
  • 10
  • 22
  • Doesn't work in SQL Server. `LIMIT` is not supported and `rand()` is not evaluated for each row. – Martin Smith Oct 29 '11 at 13:40
  • 1
    stil he dont deserve a downvote as he clearly mentioned that he did it in MySQL – Sandy Oct 29 '11 at 13:42
  • 3
    @rapsalands - The question was about SQL Server 2005 though so an answer that works in MySQL is tangential at best and not useful in answering the question asked, it just adds unneeded noise. – Martin Smith Oct 29 '11 at 16:27
  • ya...but still I believe its fine to give relative answer, which may be definitely be helpful for few – Sandy Oct 29 '11 at 18:03
  • 2
    @rapsalands - I don't. If every time someone asks a SQL Server question people are going to respond with answers for Oracle, PostgresSQL, MySQL etc it just fills the site up with irrelevant content. There are plenty of answers covering how to do this in MySQL already. – Martin Smith Oct 29 '11 at 19:15