Bird
Raised Fist0
PostgreSQLquery~5 mins

GREATEST and LEAST functions in PostgreSQL - Time & Space Complexity

Choose your learning style10 modes available
LearnWhyDeepVisualTryChallengeProjectRecallTime
Time Complexity: GREATEST and LEAST functions
O(n)
Understanding Time Complexity

We want to understand how the time it takes to find the greatest or least value changes as we give more values to compare.

How does the work grow when using GREATEST or LEAST with more inputs?

Scenario Under Consideration

Analyze the time complexity of the following code snippet.


SELECT GREATEST(score1, score2, score3, score4, score5) AS max_score,
       LEAST(score1, score2, score3, score4, score5) AS min_score
FROM player_scores;

This code finds the highest and lowest scores among five columns for each row in the table.

Identify Repeating Operations

Identify the loops, recursion, array traversals that repeat.

  • Primary operation: Comparing each value to find the greatest and least.
  • How many times: Each row compares all given values once.
How Execution Grows With Input

As you add more values to compare, the number of comparisons grows roughly in a straight line.

Input Size (n)Approx. Operations
54 comparisons
109 comparisons
10099 comparisons

Pattern observation: The number of comparisons increases directly with the number of values.

Final Time Complexity

Time Complexity: O(n)

This means the time to find the greatest or least value grows linearly as you add more values to compare.

Common Mistake

[X] Wrong: "GREATEST and LEAST instantly find the answer no matter how many values there are."

[OK] Correct: The functions must check each value to be sure which is largest or smallest, so more values mean more work.

Interview Connect

Understanding how these functions scale helps you explain performance when working with many columns or values, showing you know how database operations grow with input size.

Self-Check

"What if we used GREATEST and LEAST on values stored in an array instead of separate columns? How would the time complexity change?"

Practice

(1/5)
1. What does the GREATEST function do in PostgreSQL?
easy
A. Counts the number of values in a list
B. Returns the smallest value from a list of values
C. Returns the largest value from a list of values
D. Calculates the average of values in a list

Solution

  1. Step 1: Understand the function purpose

    The GREATEST function compares all given values and finds the largest one.

  2. Step 2: Compare with other options

    LEAST returns the smallest, counting and averaging are different functions.

  3. Final Answer:

    Returns the largest value from a list of values -> Option C

  4. Quick Check:

    GREATEST = largest value [OK]
Hint: GREATEST picks the biggest value from all inputs [OK]
Common Mistakes:
  • Confusing GREATEST with LEAST
  • Thinking it counts or averages values
  • Assuming it returns a boolean
2. Which of the following is the correct syntax to find the smallest value among 5, 10, and 3 using PostgreSQL?
easy
A. SELECT LEAST[5, 10, 3];
B. SELECT LEAST(5, 10, 3);
C. SELECT LEAST{5, 10, 3};
D. SELECT LEAST 5, 10, 3;

Solution

  1. Step 1: Recall correct function call syntax

    PostgreSQL functions use parentheses with comma-separated values.

  2. Step 2: Check each option

    Only SELECT LEAST(5, 10, 3); uses parentheses correctly; others use brackets, braces, or no parentheses.

  3. Final Answer:

    SELECT LEAST(5, 10, 3); -> Option B

  4. Quick Check:

    Function calls need parentheses [OK]
Hint: Use parentheses () for function arguments in SQL [OK]
Common Mistakes:
  • Using square brackets or braces instead of parentheses
  • Omitting parentheses around arguments
  • Separating arguments incorrectly
3. What will be the result of this query?
SELECT GREATEST(7, 2, 9, 4) AS max_val, LEAST(7, 2, 9, 4) AS min_val;
medium
A. max_val = 9, min_val = 2
B. max_val = 7, min_val = 4
C. max_val = 4, min_val = 2
D. max_val = 9, min_val = 4

Solution

  1. Step 1: Evaluate GREATEST function

    Among 7, 2, 9, 4, the largest value is 9.

  2. Step 2: Evaluate LEAST function

    Among the same values, the smallest is 2.

  3. Final Answer:

    max_val = 9, min_val = 2 -> Option A

  4. Quick Check:

    GREATEST=9, LEAST=2 [OK]
Hint: GREATEST picks max, LEAST picks min from values [OK]
Common Mistakes:
  • Mixing up max and min values
  • Choosing middle values instead of extremes
  • Ignoring all values except first or last
4. Identify the error in this query:
SELECT GREATEST(4, 8, 'ten') AS max_value;
medium
A. Missing closing parenthesis
B. No error, query runs fine
C. GREATEST function requires at least four arguments
D. Mixing integer and string types causes an error

Solution

  1. Step 1: Check argument types

    GREATEST compares values of the same type; mixing integers and strings causes a type error.

  2. Step 2: Verify syntax and argument count

    Parentheses are correct and function accepts any number of arguments greater than zero.

  3. Final Answer:

    Mixing integer and string types causes an error -> Option D

  4. Quick Check:

    Type mismatch causes error [OK]
Hint: All arguments must be same type for GREATEST/LEAST [OK]
Common Mistakes:
  • Assuming mixed types work without error
  • Thinking function needs fixed number of arguments
  • Ignoring type errors in SQL
5. You have a table scores with columns math, science, and english. Which query correctly finds the highest and lowest score for each student?
hard
A. SELECT student_id, GREATEST(math, science, english) AS highest, LEAST(math, science, english) AS lowest FROM scores;
B. SELECT student_id, MAX(math, science, english) AS highest, MIN(math, science, english) AS lowest FROM scores;
C. SELECT student_id, GREATEST(math, science, english) AS lowest, LEAST(math, science, english) AS highest FROM scores;
D. SELECT student_id, MAX(math) AS highest, MIN(english) AS lowest FROM scores;

Solution

  1. Step 1: Understand GREATEST and LEAST usage

    They compare multiple columns per row to find max and min values.

  2. Step 2: Analyze each option

    SELECT student_id, GREATEST(math, science, english) AS highest, LEAST(math, science, english) AS lowest FROM scores; correctly uses GREATEST and LEAST on columns per student. SELECT student_id, MAX(math, science, english) AS highest, MIN(math, science, english) AS lowest FROM scores; uses MAX/MIN incorrectly with multiple columns. SELECT student_id, GREATEST(math, science, english) AS lowest, LEAST(math, science, english) AS highest FROM scores; swaps highest and lowest. SELECT student_id, MAX(math) AS highest, MIN(english) AS lowest FROM scores; only compares single columns, not all three.

  3. Final Answer:

    SELECT student_id, GREATEST(math, science, english) AS highest, LEAST(math, science, english) AS lowest FROM scores; -> Option A

  4. Quick Check:

    GREATEST/LEAST compare columns per row [OK]
Hint: Use GREATEST/LEAST to compare multiple columns in one row [OK]
Common Mistakes:
  • Using MAX/MIN with multiple columns incorrectly
  • Swapping highest and lowest functions
  • Comparing only one column instead of all
Recommended

Ready to prove it?

Pass the test and this pattern moves unknown strong while earning +1 Grip toward your certificate.

10 questions~5 mineasy hard