WHERE: Filtering Numbers with Ranges and Math

We’ve been querying the Tags table, filtering by TagName, but let’s just go back to the Tags table. Here’s the results from:

SELECT * FROM dbo.Tags;

1	SELECT * FROM dbo.Tags;

See the Count? That’s the number of times the tag has been used.

WHERE with Number Comparisons

Let’s find the really popular tags – the ones that have been used over 100,000 times:

SELECT * FROM dbo.Tags WHERE Count &gt; 100000;

1	SELECT * FROM dbo.Tags WHERE Count > 100000;

Note that I’m not using a comma or a period, like 100,000 – SQL Server doesn’t like it when you stick punctuation in there unless we’re dealing with decimal places, like 3.14159. (Or 3,14159 depending on your location. SQL Server is pretty smart like that.)

Run this query at Data.StackExchange.com, and you get:

Presto, we get a few dozen really popular tags.

Similarly, all of these will work:

SELECT * FROM dbo.Tags WHERE Id &lt; 100;
SELECT * FROM dbo.Tags WHERE Id &gt;= 300;
SELECT * FROM dbo.Tags WHERE Id BETWEEN 42 AND 200;
SELECT * FROM dbo.Tags WHERE Id + 100 = 122;

SELECT * FROM dbo.Tags WHERE Id < 100;

SELECT * FROM dbo.Tags WHERE Id >= 300;

SELECT * FROM dbo.Tags WHERE Id BETWEEN 42 AND 200;

SELECT * FROM dbo.Tags WHERE Id + 100 = 122;

Granted, of course, that last one is an awfully odd way to filter Tags, but it’ll work.

For more details about which operators you can use, check out Books Online:

WHERE with Column Comparisons

You can also do comparisons between columns. Let’s find tags where their Id is higher than the number of times the tag has been used:

SELECT * FROM dbo.Tags WHERE Id &gt; Count;

1	SELECT * FROM dbo.Tags WHERE Id > Count;

Run that query, and we get some new tags that haven’t been used too often:

Now, you try.

Click Users at the top of StackOverflow, and check out the list of users:

Write a query to show only the users with a Reputation score higher than 250,000 points.

When you’re done with that, move on to the next lesson.