Getting An Absolute Record No

Does anyone know how I can get just one record returned from a select statement - not by doing set rowcount, but saying I want, for example the third record returned.

I'm trying to do a stored procedure that does a select of current records that will reduce the output to roughly 24 records and then get a random record from this - I need to do it all in a stored procedure.

What I have so far is :

BEGIN
DECLARE @Count int
DECLARE @RowCount int
DECLARE @IDint

/* Randomise the Data */
SELECT @Count = Count(*) FROM MyTable WHERE fRotation = 1

SELECT @ID = Rand() * @Count

END

As you can see, @ID will hold a record no.

I cant use batch cursors as they are giving me problems - work for a short while and then they mess up.

Any ideas anyone?

Thanks

Rich Hopwood