Skip to content
TheChatArchiveThe Chat Archive
gpt-4.1-2025-04-14· OpenAIHugging Face LMArena Human Preference 140K

Can we use OVER() without any argument?

From a Chatbot Arena battleThe same person sent this prompt to two anonymous models at once. Compare it with grok-3-mini-beta’s answer.
  1. User

    Can we use OVER() without any argument?

  2. Assistant

    Yes, you can use OVER() without any arguments in SQL, but its behavior depends on the window function you are using.


    1. Aggregate Window Functions

    For functions like SUM(), AVG(), COUNT(), etc., you can use OVER() with empty parentheses to calculate the value over the entire result set (no partitioning, no ordering).

    Example:

    SELECT Name, Salary, SUM(Salary) OVER() AS total_salary
    FROM employee;
    

    This will show the total salary for all employees in every row.


    2. Ranking Functions (RANK(), ROW_NUMBER(), etc.)

    For ranking functions, ORDER BY inside OVER() is required to define the order in which the ranking is applied.
    If you write:

    SELECT Name, RANK() OVER() AS rnk FROM employee;
    

    Most SQL databases will throw an error because RANK() needs an order to assign ranks.


    3. Summary Table

    Function TypeOVER() with no arguments allowed?Notes
    Aggregate (SUM, AVG)YesOperates over the whole result set
    Ranking (RANK, ROW_NUMBER)NoNeeds ORDER BY inside OVER()
    Analytic (LEAD, LAG)NoNeeds ORDER BY inside OVER()

    Conclusion

    • Yes: For aggregate window functions, OVER() with no arguments is valid.
    • No: For ranking and analytic functions, you must provide at least an ORDER BY clause inside OVER().

    If you have a specific function in mind, let me know and I can give an example!

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.