Selecting top rows or random rows

We all come across the need to select top rows and/or random rows from our tables. I wrote a blog about Dynamic Paging in the past and in that showed a way of selecting a range of rows both in SQL Server and Oracle. Let's extend the thought and select either top rows or random rows. Take a look:

--SQL Server: Top 10 of SomeColumn

Select Top 10 * From myTable Where MyColumn = 'MyCondition' Order By SomeColumn Desc

--Oracle: Top 10 of SomeColumn

Select *

From (Select Row_Number() Over (Partition By 'kigo' Order By SomeColumn Desc) myTops,

Column1, Column2, Column3

From myTable Where MyColumn = 'MyCondition')

Where myTops < 10

--Oracle: Gets you 1% records

Select * From myTable Sample(1)

--SQL Server: Random 10

Select Top 10 * From myTable Where MyColumn = 'MyCondition' order by NewID()

--Oracle: Random 10

Select *

From (Select Row_Number() Over (Partition By 'kigo' Order By dbms_random.Value()) myTops,

Column1, Column2, Column3

From myTable Where MyColumn = 'MyCondition')

Where myTops < 10

Love coding!