Skip to main content

Documentation Index

Fetch the complete documentation index at: https://docs.omniql.com/llms.txt

Use this file to discover all available pages before exploring further.

Perform calculations across rows related to the current row.

​
Supported Functions

FunctionDescription
ROW NUMBERSequential row numbers
RANKRank with gaps for ties
DENSE RANKRank without gaps
LAGPrevious row value
LEADNext row value
NTILEDivide into buckets

​
Basic Syntax

:GET Entity WITH *, ROW NUMBER OVER (ORDER BY field) AS row_num
:GET Entity WITH *, RANK OVER (PARTITION BY field ORDER BY field) AS rank
Use * to include all columns, or specify columns explicitly: 
:GET Entity WITH id, name, ROW NUMBER OVER (ORDER BY field) AS row_num

​
ROW NUMBER

Assign sequential numbers to rows. 
:GET User WITH *, ROW NUMBER OVER (ORDER BY created_at) AS row_num
DatabaseOutput
PostgreSQLSELECT *, ROW_NUMBER() OVER (ORDER BY created_at) AS row_num FROM users

​
With Partition

:GET User WITH *, ROW NUMBER OVER (PARTITION BY department ORDER BY salary DESC) AS row_num
DatabaseOutput
PostgreSQLSELECT *, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num FROM users

​
RANK

Assign rank with gaps for ties. 
:GET Product WITH *, RANK OVER (PARTITION BY category ORDER BY price DESC) AS rank
DatabaseOutput
PostgreSQLSELECT *, RANK() OVER (PARTITION BY category ORDER BY price DESC) AS rank FROM products

​
DENSE RANK

Assign rank without gaps for ties. 
:GET Product WITH *, DENSE RANK OVER (ORDER BY price DESC) AS dense_rank
DatabaseOutput
PostgreSQLSELECT *, DENSE_RANK() OVER (ORDER BY price DESC) AS dense_rank FROM products

​
Rank Comparison

ScoreROW NUMBERRANKDENSE RANK
100111
100211
90332
80443

​
LAG

Access previous row value. 
:GET Order WITH *, LAG amount OVER (ORDER BY created_at) AS prev_amount
DatabaseOutput
PostgreSQLSELECT *, LAG(amount) OVER (ORDER BY created_at) AS prev_amount FROM orders

​
LEAD

Access next row value. 
:GET Order WITH *, LEAD amount OVER (ORDER BY created_at) AS next_amount
DatabaseOutput
PostgreSQLSELECT *, LEAD(amount) OVER (ORDER BY created_at) AS next_amount FROM orders

​
NTILE

Divide rows into buckets. 
:GET User WITH *, NTILE 4 OVER (ORDER BY salary) AS quartile
DatabaseOutput
PostgreSQLSELECT *, NTILE(4) OVER (ORDER BY salary) AS quartile FROM users
Divides users into 4 salary quartiles.

​
Complete Example

:GET Order WITH 
  id, 
  user_id, 
  amount,
  ROW NUMBER OVER (PARTITION BY user_id ORDER BY created_at) AS order_num,
  RANK OVER (ORDER BY amount DESC) AS amount_rank
  WHERE created_at > "2024-01-01"
  ORDER BY user_id, created_at

​
MongoDB Note

MongoDB has limited window function support (5.0+). Complex window functions may require aggregation pipelines.

​
Limitations

Current implementation supports:
  • PARTITION BY
  • ORDER BY within OVER clause
Not currently supported:
  • Frame clauses (ROWS BETWEEN)
  • FIRST_VALUE, LAST_VALUE
  • Aggregate functions as window functions (SUM() OVER, AVG() OVER)

​
Next Steps

Insert Data

Create new records

Update Data

Modify existing records