Stack Overflow
Stack Overflow

Questions tagged [over-clause]

This tag is related to SQL window functions

32 questions
5
votes
4 answers

Retreive date from rolling maximum

I have a SQL Server table with a date (session_date) and a value (price). session_date price 2022-12-31 10 2022-12-30 9 2022-12-29 5 2022-12-28 7 2022-12-27 2 2022-12-26 9 2022-12-25 3 2022-12-24 1 I want to compute the…
NassimH
  • 462
  • 3
  • 13
5
votes
2 answers

how to create a pivot table with dynamic column using linq tree expression

I'm writing an asp.net C# web application; i have an in-memory datatable named 'table1' having three columns 'country', 'productId' and 'productQuantity'; i want to pivot that table in order to obtain a new table (suppose 'table2') having the first…
user598956
  • 51
  • 1
  • 3
3
votes
1 answer

SQL partition achieving "second_value" etc

I'm calculating customers retention and want to segment my customers according to behavior in first, second, third and so-on purchases. For example: using first_value(had_coupon) over (partition by customer_id order by order_date DESC) I can…
oshrim
  • 31
  • 4
3
votes
5 answers

Order by multiple columns in SQL Server

I have a table in SQL Server in which I need to select the data, sorted based on a value. For example, If the sort value is 1, then I need to sort by column1, column2 and then column3. If the sort value is 2, then I need to sort by column2, column1…
Bhavika
  • 51
  • 1
  • 5
3
votes
2 answers

How to remove the duplicate records in select query over clause

I am having Transactions table as follows in SQL SERVER. UserID TranDate Amount 1 | 2015-04-01 | 0 1 | 2015-05-02 | 5000 1 | 2015-09-07 | 1000 1 | 2015-10-01 | -4000 1 | 2015-10-02 | …
Jenish Rabadiya
  • 6,708
  • 6
  • 33
  • 62
3
votes
2 answers

Issue with Window Function in SQL Server 2008 R2

I get an execution error in following SQL script: SELECT TOP 1 PERCENT a.accode, a.voucherdate, a.credit, a.Debit, SUM(a.Debit) OVER (ORDER BY [a.accode],[a.voucherdate]) AS rdr FROM VoucherMain AS a ORDER BY a.accode,…
Nilkanth Desai
  • 71
  • 2
  • 4
2
votes
0 answers

ROUND() function doesn't work with OVER clause

The code below doesn't work when I replace SUM(Sales) with ROUND(SUM(Sales),2), works otherwise. Sales field type = Double. SELECT row_number() OVER (PARTITION BY Region) as Row_No, Name as Product, Category, Sales, SUM(Sales) OVER (PARTITION BY…
Sachin D
  • 73
  • 1
  • 2
  • 8
2
votes
12 answers

Why does my query return errors on Codility's test editor?

My solution to Codility / Exercises 6 SQL / SqlEventsDelta in SQLite works in local DB Browser but not in online Codility test editor. How can I solve this? I want to use my own SQLite code: WITH cte1 AS ( SELECT *, CASE WHEN e2.event_type =…
yunjeong park
  • 23
  • 1
  • 4
2
votes
1 answer

SQL DENSE_RANK and PARTITION BY

I do not fully understand the query results listed for the below: CREATE TABLE #tmpAccountsTable (ID INT PRIMARY KEY, AccountID INT, AccountName varchar(20), CompanyID int, CompanyName varchar(50)) INSERT INTO #tmpAccountsTable (ID, AccountID,…
monstertjie_za
  • 7,277
  • 8
  • 42
  • 73
2
votes
2 answers

limit data within an over statement in oracle

I want to Aggregate a column over timestamps. Here an example: Table contains columns like col1, col2, ..., col_ts (timestamp column). SELECT SUM(col1) OVER (ORDER BY col_ts ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) SUM1, SUM(col2) OVER (ORDER BY…
Fulley
  • 73
  • 6
2
votes
1 answer

Missing window specification for this function

Now getting the following error: ORA-30484: missing window specification for this function 30484. 00000 - "missing window specification for this function" *Cause: All window functions should be followed by window specification, like…
user3697763
  • 21
  • 1
  • 1
  • 3
2
votes
1 answer

SQL - OVER Clause range between 2 times

I am trying to use the over function to count the number of events which start between the start and end date of the current row for all rows. I.e. a concurrent counter. Ultimately I will be looking to find the Maximum Concurrent events per day or…
Alister Mathie
  • 21
  • 1
  • 2
1
vote
0 answers

MySQL window function with parameter based frame size

I come from MS SQL Server and I'm relatively new to MySQL / MariaDB 10 (at least in a deeper way than just "SELECT * FROM [Table]"). I now searched for several hours in Google and StackOverflow, but I haven't found a soluton to my problem yet. If…
AtoMedia Design
  • 31
  • 3
1
vote
1 answer

When and how to use Window Functions in PostgreSQL?

I've been learning Window Functions (e.g., OVER, RANK, PARTITION BY clauses) in PostgreSQL but am still confused when and how to use them and what would the resulted outputs mean? Does anyone have some summarised explanations on them? EXAMPLE:…
Ilovenoodles
  • 83
  • 1
  • 7
1
vote
1 answer

Windowed Functions and Query Optimizer

I have a table with the following structure. |anId| aDate|aNumber| ------------------------- | 1|2018-01-20| 100| | 1|2019-01-01| -100| | 1|2019-02-01| 10| | 2|2019-01-02| 40| I have a query to return, on a specific date,…
Jamie
  • 168
  • 10
1
2 3