Selecting every nth row from SQL Server 2008 query result where table does not have row id column

Question

I feel so close... I think my problem is how I'm using the MOD function combined with the `ROW_NUMBER()` function, but I don't understand what I'm doing wrong. I'm using the `ROW_NUMBER()` function because I need a way to select every "nth" row. I've read the other pages about this (I used them to create my SQL)...but I'm getting an error from SQL Server. I need the inner table join (2 instances of the table `Tick_OneMin`, `H1` and `H2`) to get prices of different securities at the same time snapshot. If I comment out the line with the `MOD` function...the SQL executes fine...but if I put it in....SQL Server throws the error message: An expression of non-boolean type specified in a context where a condition is expected, near 'MOD'. Here is my attempt at the SQL-- ``` SELECT ROW_NUMBER() OVER (ORDER BY H1.CombDateTime ASC) AS RowID, H1.CombDateTime, H1.Close_PX as 'TYA_Close', H2.Close_PX 'ESA_Close' FROM Tick_OneMin as H1, Tick_OneMin as H2 WHERE H1.Ticker = 'TYA' AND H2.Ticker = 'ESA…

Accepted Answer

You can't reference an alias defined in the `SELECT` clause in the `WHERE` clause, since `WHERE` is parsed first. One workaround is to use a subquery or CTE:

```
WITH x AS
(
 SELECT ROW_NUMBER() OVER (ORDER BY H1.CombDateTime ASC) AS RowID,
  ... rest of query
)
SELECT CombDateTime, TYA_Close, ESA_Close --, RowID
FROM x
WHERE RowID % 4 = 0
ORDER BY CombDateTime;
```

Note also what Martin and Marc have pointed out - SQL Server uses `%` not the `MOD` operator you're bringing in from VB or elsewhere.

Selecting every nth row from SQL Server 2008 query result where table does not have row id column

Solution

Problem