Recently I came across a cool query by Peter where he displayed how to do it based on a category. I have modified the query to show you how random records can be displayed from a table, partitioned on a category. We will also see how to delete these records.
If you observe, there are 8 records of ErrorCategory 1 and 4 records of ErrorCategory 2. In order to display 50% random records from both Error Category 1 and 2, use this query
QUERY to Select Random Records based on a category
Similarly, now if you have to delete random records in a table based on a category, here's how to do so
QUERY to Delete Random Records from a table based on a category
Observe that Both ErrorCategory 1 and 2 had even set of records (8 and 4 each). If there are odd number of records, the results will differ.
Note: SQL Server 2005/2008 also has the TABLESAMPLE clause that can be used to do random sampling. I will explain this feature in my next post
Did you like this post?
|subscribe via rss||subscribe via e-mail|
|print this post||follow me on twitter|