T-SQL and every other row... »

Every so often I jump into Google analytics and try to figure out just who is hitting this blog and what they are reading...today was one of those random days.

In looking at the logs, I noticed that someone stumbled here via Google while attempting to find a solution on how to get every other row in a table with T-SQL...and though they are probably already long gone, I thought I would give my quick answer anyway (who knows maybe someone else will have the same question down the road?)...

Basically, any time you are thinking about an every other situation (or any skip X type of thing for that matter), you should be thinking about a modulo ... and in T-SQL you use the % symbol to do modulo.

So here is the quick answer:

select * from (select *, (account_id % 2) mod from account) tmp where mod = 0

What you are basically doing is dividing the account_id of each record by 2...if there is a remainder, then it's an odd number...if there is not a remainder, then it's an even number...in this example, we want all the records with even numbers so we check that the remainder is zero (0).

Now technically this is not truly 'every other' record...because it's based on your account_id and assumes that you are not missing any account_id in your sequence of records (for example you could have deleted account_id = 2 and so now there is no gap between 1 and 3 and yet neither 1 or 3 would be pulled up by this query)...

But on a generic level it will get you what you want.

If you really want to get fancy you can combine the count() function (with a group by) to dynamically generate the number that you then apply a modulo to...and that should work for true 'every other'...but I'll leave that as something for you to try and implement/play with...for some ideas on getting started with this approach check out http://support.microsoft.com/default.aspx?scid=KB;EN-US;q186133

Also I feel like I should at least note the fact that it's probably VERY rare that you would need to do something like this 'every other row' stuff...

It's much more likely that you should just write a better where clause (perhaps spend some more time thinking about what data you are really trying to get at), or maybe a random chunk of rows...but that's just my two cents. And it's nice to know that if you do find a reason to get at every other row...now you know you can!