<body><script type="text/javascript"> function setAttributeOnload(object, attribute, val) { if(window.addEventListener) { window.addEventListener('load', function(){ object[attribute] = val; }, false); } else { window.attachEvent('onload', function(){ object[attribute] = val; }); } } </script> <div id="navbar-iframe-container"></div> <script type="text/javascript" src="https://apis.google.com/js/platform.js"></script> <script type="text/javascript"> gapi.load("gapi.iframes:gapi.iframes.style.bubble", function() { if (gapi.iframes && gapi.iframes.getContext) { gapi.iframes.getContext().openChild({ url: 'https://www.blogger.com/navbar/8776281485402821239?origin\x3dhttp://mythoughtsaboutcode.blogspot.com', where: document.getElementById("navbar-iframe-container"), id: "navbar-iframe" }); } }); </script>

.

About

My Name is Justin Copeland and I am a .NET Developer at Terralever. We are a web marketing/development company located in Tempe, Arizona.

Recent

Here are some of my most recent blog entries. Check out the freshest ones first then hit up the archives.

Archives

Other Blogs

Selecting Random Row from a SQL SELECT Statement
Friday, March 23, 2007

Well I just found something cool as I was looking for a way to select some data from a SQL Server table and only return 1 random row. Pretty easy too:

SELECT TOP 1 columnName FROM table ORDER BY NEWID()

Works like a charm.

Labels: , ,

« Home
Vista, IIS 7, and ASP.NET 1.1 Playing Nice Together »
Who Am I? »

You can leave your response or submit to Digg by using the links below.
Comment | Digg This | Go to end
  • Anonymous Anonymous said this:
    March 23, 2007 at 11:28 PM  

    One thing you might want to watch out for in this case would be using this in a table with a lot of rows that aren't excluded. NEWID() generates a 128-bit value, which is already going to generate a slow comparison (that's up to four machine words on 32-bit servers!), not to mention the fact that every row returned will have NEWID() called for it.

    There are a couple other ways that might be better. If you need a single random row, you can get max row ID first (SELECT MAX(identity-column) FROM table;), perform a random operation with that value as the cap (new Random().Next(maxID + 1);), and then retrieve the first row with the ID greater than or equal to that ID (SELECT TOP(1) * FROM table WHERE identity >= randomValue;). This seems to be a safer and somewhat more efficient way to keep load off of the database server at the possible risk of some loss of randomness, as rows with many IDs missing before them are more likely to be picked. For instance:

    |-|2|-|-|5|-|-|-|-|-|11

    In this case, row 11 is most likely to be picked because 6-11 can be the random choice and 11 will be chosen. 2 is the least likely, etc.

    In any case, each method has its ups and downs based on the assumptions you can make about your data. If you know that your data isn't going to suffer from many DELETEs, my suggested method is pretty safe; on the other hand, if you're going to have a lot of fragmented IDs, something else such as the NEWID() strategy might be better. :) top

  • Blogger Some Guy said this:
    April 2, 2007 at 12:08 PM  

    So this is what I ended up doing so all my followers of this blog (5 I think) will know.

    SELECT TOP 1 FROM tableName ORDER BY RAND((1000 * MyPK) * DATEPART(millisecond, GETDATE()))

    Seemed to work and hopefully isn't taking up a bunch of resources on my SQL Server. top

  • Blogger Some Guy said this:
    April 12, 2007 at 10:42 AM  

    I just noticed that my previous comment is slightly wrong. Forgot to add the asterisk before the "FROM".

    SELECT TOP 1 * FROM resource ORDER BY RAND((1000 * MyPK) * DATEPART(millisecond, GETDATE())) top

Read This

All entries in this blog are my opinion and don't necessarily reflect the opinion or views of my employer (Terralever).

This is where I talk about the little bit of .NET code that I actually know and different techy things that interest me. Feel free to tell all your friends about my little blog and hopefully soon, it won't be so little.

My Thoughts About Code | feed

5ThirtyOne and Blogger Templates design | Top