Random ActiveRecord Collection

I need an ActiveRecord Collection of 2 objects that are selected at Random from the Student table where the FirstName is "John".

So, this is what I have so far
//The following gives me all the objects where FirstName is "John" -- but I need only 2 randomly selected records
StudentCollection _StudentCollection = new StudentCollection().Where(Student.FirstName, "John").Load(); 

//Other part I might need to use is the Query object
Query _Query = new Query():
_Query.Top = 2;
_Query.OrderBy = OrderBy.Asc("newid");

How do I combine the two so I can use the query object? Or is there is different way of doing what I am trying to achieve?

Thanks for any help in advance!

 StudentCollection s = new SubSonic.Select.From("Student")
                     .Where("FirstName")
                     .IsEqualTo("John")
                     .Top("2")
                     .OrderAsc("NewId")
                     .ExecuteAsCollection<StudentCollection>();

But, that doesn't solve your "Random" issue.

For tthat you could either use the RND function in a SQLServer stored procedure or return all rows and write c# logic to select 2 random rows.

Actually, what he is doing should solve it, BUT, I think the Orderby Clause should be:

.OrderAsc("NewID()")

That will cause the SQL Server to generate a random GUID for each row, and order it by that, and the TOP 2 prunes off the 2 as expected.

The NewID is a function, not a field, so I think you have to include the paranthesis.

That solves the random issue.

NewID() is a function, not a column. If the query Geoff suggested works, it should return random results.

ranomore:

NewID() is a function, not a column

I stand corrected by DiverKas and ranmore

In which case, I agree, the query I posted should return 2 random rows

Thanks everyone for you help! I really appreciate it!

GeoffAtDatagaard, The NewId() works for randomness but "Top" is coming up as undefined. Any suggestions?

Thanks

 Top is a member of Select but not Query.

Which one are you using and have you got the current release ?

Thank you. I had to move "Top" to the beginning as it is a member of Select and not SqlQuery. "OrderAsc", "Where" all return SqlQuery thats why it wasent finding it. The below works!

StudentCollection s = new SubSonic.Select().Top("2")
                     .From("Student")
                     .Where("FirstName")
                     .IsEqualTo("John")
                     .OrderAsc("NewId")
                     .ExecuteAsCollection<StudentCollection>();