CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012
GeekyOmega
GeekyOmega

Reputation: 1339

How to write an SQL query to have alternating pattern between rows (like -1 and 1)?

I cannot get an alternating pattern of 1 an -1 with my database.

This explains what I am trying to do. 

ID  Purpose  Date      Val 
1   Derp     4/1/1969   1
1   Derp     4/1/1969  -1
2   Derp     4/2/2011   1
2   Derp     4/2/2011  -1

From a database that is something like 

ID  Purpose  Date      
1   Derp     4/1/1969  
1   Herp     4/1/1911  
2   Woot     4/2/1311  
2   Wall     4/2/211

Here is my attempt:

SELECT 
     ID 
     ,Purpose 
     ,Date
     ,Val as 1 
FROM (
     SELECT FIRST(Purpose) 
     FROM DerpTable WHERE Purpose LIKE '%DERP%' 
     GROUP BY ID, DATE) as HerpTable, DerpTable 
 WHERE HerpTable.ID = DerpTable.ID AND DerpTable.ID = HerpTable.ID

This query does not work for me because my mssm does not recognize 'FIRST' or 'FIRST_VALUE' as built in functions. Thus, I have no way of numbering the first incident of derp and giving it a value.

Problems:

What I need is a fresh perspective and assistance. Am I making this too hard?

Upvotes: 0

Views: 523

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270653

You can use first_value() in SQL Server 2012. I'm not sure what the WHERE condition is in your query, but the following should return your desired results:

  SELECT ID,
         FIRST_VALUE(Purpose) OVER (PARTITION BY ID ORDER BY DATE) as Purpose,
         DATE,
         2 * ROW_NUMBER() OVER (PARTITION BY ID ORDER BY DATE) - 1
  FROM DERPTABLE

Upvotes: 3

Dave Zych
Dave Zych

Reputation: 21897

Use a subquery along with ROW_NUMBER and the modulo operator:

select
    ID,
    Purpose,
    Date,
    case when rownum % 2 = 0 then 1 else -1 end as Val
from (
    SELECT 
        ID 
        ,Purpose 
        ,Date
        ROW_NUMBER() over (order by ID) as rownum
    FROM (
        SELECT 
            ID,
            Purpose,
            Date
        FROM DerpTable WHERE Purpose LIKE '%DERP%' 
        GROUP BY ID, DATE) as HerpTable, DerpTable 
        WHERE HerpTable.ID = DerpTable.ID AND DerpTable.ID = HerpTable.ID
) [t1]

ROW_NUMBER will assign a value to each row, in this case it's an incrementing value. Using the modulus with 2 allows us to check if it's even or odd and assign 1 or -1.

Note: I don't know if this query will run since I don't know the architecture of your database, but the idea should get you there.

Upvotes: 3

caleb.breckon
caleb.breckon

Reputation: 1364

Why not add an incremental column, update the table using modulo to determine if it's even or odd, then drop the column?

Upvotes: 1

Related Questions