# Window

> Ranking and neighbours inside a query's result. The indexed `Select((s, i) => …)` over an ordered chain is C#'s own spelling of a row number; the `Window.*` functions add what C# has no spelling for — per-partition ranks, the previous/next row's value, running aggregates — all computed by the database in the same statement.

<!-- id: query-window · area: query · stability: stable · html: https://osysharp.com/reference/query/window/ -->

## Summary        {#summary}
A window function computes a value **about a row's place among the other rows** — its rank, the previous row's
value, a per-group aggregate — without collapsing the rows the way `GroupBy` does. Every row still comes back;
each carries its answer.

## Signature      {#signature}
```osy syntax
<ordered query>.Select((s, i) => …)                          // i = the 0-based position, C#'s own spelling
Window.RowNumber(orderBy: k)                                  → int
Window.Rank(orderBy: k, partitionBy: p)                       → int    // ties share, next rank skips
Window.DenseRank(orderBy: k, partitionBy: p)                  → int    // ties share, no gap
Window.Lag(value, orderBy: k, partitionBy: p)                 → T?     // the PREVIOUS row's value — null at the edge
Window.Lead(value, orderBy: k, partitionBy: p)                → T?     // the NEXT row's value — null at the edge
Window.Sum(value, orderBy: k, partitionBy: p) · Avg · Min · Max · Count
Window.Sum(value, orderBy: k, rowsBefore: n, rowsAfter: m)   // a sliding frame — aggregators only
```
`orderByDescending:` orders the window the other way. `partitionBy:` is optional — without it the window is the
whole result; `partitionBy: new { a, b }` partitions on the pair. `rowsBefore:`/`rowsAfter:` bound an aggregate
to the rows around the current one; without them an ordered aggregate is a running total. `Window.*` lives
**inside a `.Select(…)` projection** over stored rows, nowhere else.

## Description    {#description}

### How do I number the rows?   {#row-number}
With the index C# already gives a `Select` — legal over stored rows once the chain names an order (a table has no
position until you say which):

```osy title="a leaderboard, numbered in points order" test app=query-window
entity Score {
  [Required] string Player;
  [Required] string Region;
  [Required] string Tier;
  int Points;
}

string Board() {
  var rows = Score.OrderByDescending(s => s.Points)
                  .Select((s, i) => new { Line = (i + 1) + ". " + s.Player });
  var outText = "";
  foreach (var r in rows) { outText = outText + r.Line + "\n"; }
  return outText;
}
```

Without the `OrderBy` this refuses at compile, with the order as the remedy.

### How do I rank within groups?   {#partitioned}
`partitionBy:` restarts the window per group — every region gets its own ranking, in one statement:

```osy title="per-region ranks, every row still a row" test app=query-window
string RegionBoards() {
  var rows = Score.OrderBy(s => s.Region)
                  .Select(s => new {
                    s.Player, s.Region,
                    Rank = Window.Rank(orderByDescending: s.Points, partitionBy: s.Region),
                  });
  var outText = "";
  foreach (var r in rows) { outText = outText + r.Region + ":" + r.Player + "#" + r.Rank + "\n"; }
  return outText;
}
```

`Rank` gives ties the same number and skips the next (1, 1, 3); `DenseRank` doesn't skip (1, 1, 2);
`RowNumber` never ties.

A group keyed by **more than one column** is an anonymous object — the same spelling `GroupBy` uses for a
composite key. The window restarts wherever any part of the pair changes:

```osy title="ranks within each region AND tier" test app=query-window
string TierBoards() {
  var rows = Score.OrderBy(s => s.Region).ThenBy(s => s.Tier)
                  .Select(s => new {
                    s.Player, s.Region, s.Tier,
                    Rank = Window.Rank(orderByDescending: s.Points, partitionBy: new { s.Region, s.Tier }),
                  });
  var outText = "";
  foreach (var r in rows) { outText = outText + r.Region + "/" + r.Tier + ":" + r.Player + "#" + r.Rank + "\n"; }
  return outText;
}
```

### How do I sum a sliding window?   {#frame}
An aggregate with an `orderBy:` is a **running** total by default — every row sums itself and everything before
it. `rowsBefore:` and `rowsAfter:` narrow that to the rows around the current one, counted in the window's order:
`rowsBefore: 2` is this row and the two before it; `rowsAfter: 1` is this row and the next; both together is a
centred frame. A bound counts ROWS, not values — three rows with equal points are still three rows.

```osy title="a three-row moving average, and what is still to come" test app=query-window
string Trend() {
  var rows = Score.OrderBy(s => s.Points)
                  .Select(s => new {
                    s.Player,
                    Running = Window.Sum(s.Points, orderBy: s.Points),
                    Around  = Window.Avg(s.Points, orderBy: s.Points, rowsBefore: 1, rowsAfter: 1),
                    Ahead   = Window.Count(orderBy: s.Points, rowsAfter: 2),
                  });
  var outText = "";
  foreach (var r in rows) { outText = outText + r.Player + ":" + r.Running + "/" + r.Around + "/" + r.Ahead + "\n"; }
  return outText;
}
```

A frame belongs to the aggregators — `Sum`, `Avg`, `Min`, `Max`, `Count`. A rank is over the whole partition
and `Lag`/`Lead` reach a fixed distance already, so a bound on any of those refuses at compile; so does a frame
with no `orderBy:` (a slice of an unordered set means nothing) and a negative bound.

### How do I read the previous row?   {#lag}
`Lag` (and `Lead`) hand you a neighbouring row's value. At the window's edge there is no neighbour, so the answer
is **null** — the type says so, and you answer for it like any other absence:

```osy title="the gap to the previous score" test app=query-window
string Gaps() {
  var rows = Score.OrderBy(s => s.Points)
                  .Select(s => new {
                    s.Player,
                    Gap = s.Points - (Window.Lag(s.Points, orderBy: s.Points) ?? s.Points),
                  });
  var outText = "";
  foreach (var r in rows) { outText = outText + r.Player + ":" + r.Gap + "\n"; }
  return outText;
}
```

### Where may a window stand?   {#position}
Only inside a `.Select(…)` projection over stored rows — a window ranks a SET the database holds. Anywhere else it
refuses at compile, pointing here; over a local list, C#'s own tools (`Select((x, i) => …)` on the list, sorting,
indexing) already answer.

## See also       {#see-also}
- [Select (projections)](https://osysharp.com/reference/query/select/) — the projection a window lives in
- [OrderBy / ThenBy](https://osysharp.com/reference/query/ordering/) — the order a window ranks by