CODEWITHSUNDEEP
sqlsql-servert-sql
Mat41
Mat41

Reputation: 1267

Return a column value every n number of rows

I have a simple select on a table which does not have a primary key. I am extracting 365 values, one for each day of the year. Is it possible for me to do this in SQL Server 2008 R2. I wish to add another column called rain and populate it with a value one per week, or every seven rows. my current query is:

SELECT jDay, tMax, tMin 
FROM tableName;

It's returning just the first three columns. How do I achieve the rain column to make it look like this:

jDAy   tmax    tmin    rain
11    21.1    10.1     0     
12    22.1    11.1     0 
13    23.1     9.1     0 
14    24.1     8.1     0 
15    29.1    10.1     0 
16    21.1    11.1     0 
17    22.1    12.1     4

Edited: the solution thanks to ZLK:

SELECT 
    julDay, tMax, tMin, 
    rain = case 
              when row_number() over (order by julDay) % 7 = 0 then 4 
              else 0 
           end
FROM
    tableName 
WHERE
    .......;

Upvotes: 1

Views: 222

Answers (1)

ZLK
ZLK

Reputation: 2874

Using ROW_NUMBER() with MODULO (%), you can specify a value every X rows.

e.g.

SELECT julDay,
       tMax,
       tMin,
       rain = CASE WHEN ROW_NUMBER() OVER (ORDER BY julDay) % 7 = 0 THEN 4 ELSE 0 END
FROM tableName;

Upvotes: 1

Related Questions