Tutorial by Examples

SELECT your_columns, COUNT(*) OVER() as Ttl_Rows FROM your_data_set idnameTtl_Rows1example52foo53bar54baz55quux5 Instead of using two queries to get a count then the line, you can use an aggregate as a window function and use the full result set as the window. This can be used as a base for fur...

Let's say I have this data: Table items idnametag1exampleunique_tag2foosimple42barsimple3bazhello51quuxworld I'd like to get all those lines and know if a tag is used by other lines SELECT id, name, tag, COUNT(*) OVER (PARTITION BY tag) > 1 AS flag FROM items The result will be: idnametag...

Given this data: dateamount2016-03-122002016-03-11-502016-03-141002016-03-151002016-03-10-250 SELECT date, amount, SUM(amount) OVER (ORDER BY date ASC) AS running FROM operations ORDER BY date ASC will give you dateamountrunning2016-03-10-250-2502016-03-11-50-3002016-03-12200-1002016-03-1410...

Given this data User_IDCompletion_Date12016-07-2012016-07-2122016-07-2022016-07-2122016-07-22 ;with CTE as (SELECT *, ROW_NUMBER() OVER (PARTITION BY User_ID ORDER BY Completion_Date DESC) Row_Num FROM Data) SELECT * FORM CTE WHERE Row_Num <= n Us...

Given these sample data: IDSTATUSSTATUS_TIMESTATUS_BY1ONE2016-09-28-19.47.52.501398USER_13ONE2016-09-28-19.47.52.501511USER_21THREE2016-09-28-19.47.52.501517USER_33TWO2016-09-28-19.47.52.501521USER_23THREE2016-09-28-19.47.52.501524USER_4 Items identified by ID values must move from STATUS 'ONE' to...