CODEWITHSUNDEEP
sqlsql-server-2008max
user2036380
user2036380

Reputation: 29

SQL: Computing Max value based on Criteria

I have a table that has the following properties

  1. UserName: identifier
  2. Sequence: this is an ordered list to indicate a sequence of events
  3. Aggregated: this is also sequential. For instance if a response was taken for a particular event, then a value is present (1 indicating the first action, 2 indicating the second action, ..., 0 indicates no action was taken for that event).

I need to add another column which replaces the 0 with max value for same user up to that point. See Second table for example. For instance, even though there was no action taken on event 3, there had been 2 actions up to that point, thus 2 should be the value associated with the row.

Running SQL Server 2008 R2

| Username  | Seq | Agg|
------------------------
| Username1 |  1  | 1  |
| Username1 |  2  | 2  |
| Username1 |  3  | 0  |
| Username1 |  4  | 0  | 
| Username1 |  5  | 3  |



| Username  | Seq | Agg| Act|
-----------------------------
| Username1 |  1  | 1  | 1  |
| Username1 |  2  | 2  | 2  |
| Username1 |  3  | 0  | 2  |
| Username1 |  4  | 0  | 2  |
| Username1 |  5  | 3  | 3  |

Upvotes: 0

Views: 77

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Most dialects of SQL support ANSI standard window functions. You haven't mentioned which database, so let's assume that you are using one:

select a.*,
       max(agg) over (partition by username order by seq) as act
from atable a;

If your database doesn't support window functions (or the cumulative max), you can do this with a correlated subquery:

select a.*
       (select max(a2.agg)
        from atable a2
        where a2.id <= a.id
       ) as act
from atable a;

Upvotes: 2

Related Questions