Alexander Farber Alexander Farber - 3 months ago 8
SQL Question

Find sum of max values in a single SELECT query

I have prepared an SQL Fiddle for my question.

In a word game I run a custom function to find all words played by a user in her last turn:

app screenshot

Invalid words have a

score
of
NULL
(could be changed to
-1
if needed).

Valid words have positive
score
and if there are several matching words, then I only need the highest score (and discard the other score).

For example, if a player plays horizontal word "ab" with score 8 and vertical word "ab" with score 2, then she only gets 8 points for that turn.

Here is my test table:

CREATE TABLE words(word varchar, score integer);


And here I fill it with test data:

INSERT INTO words (word, score) VALUES
('ab', 8), -- word with higher score should be taken
('ab', 2), -- same word with lower score should be discarded
('xy', 2),
('zz', NULL); -- invalid word marked by NULL (or -1)


I can see if an invalid word has been played by

IF EXISTS (SELECT 1 FROM words WHERE score IS NULL) THEN
RAISE EXCEPTION 'Invalid word played';
END IF;


And I can remove duplicate words with
GROUP BY
:

SELECT word, max(score) as score FROM words GROUP BY word;


My question is however:

How to combine both statements above to a single
SELECT
statement, so that I:


  1. Know if an invalid word has been played

  2. The sum of played scores (so that I can update the player's score)



I am looking for a single statement, so that the custom function is not run several times and preferably without a temporary table.

The result should look like (I am going to call it from another custom PL/pgSQL function):

DECLARE
total_user_score integer;
invalid_words_found boolean;

SELECT
....., -- how to calculate this value please?
..... -- how to calculate this value please?
INTO STRICT
total_user_score,
invalid_words_found
FROM words_check_words(....); -- avoid calling this function twice

IF invalid_words_found THEN
RAISE EXCEPTION "Invalid words found";
ELSE
UPDATE games SET user_score = user_score + total_user_score;
END IF;

Answer

(edited to return a boolean for invalid_words_found)
(edited to use bool_or aggregate function)

If I understood correctly:

with cte as (
    select max(score) as score,
           bool_or(score is null) as has_invalid
      from words_check_words(....)
     group by word
)
select coalesce(sum(score), 0) as total_user_score,
       bool_or(has_invalid) as invalid_words_found
  from cte

Your custom function would only get called once.

EDIT: Integrating into your procedure, it would look something like this:

DECLARE
    total_user_score    integer;
    invalid_words_found boolean;

with cte as (
    select max(score) as score,
           bool_or(score is null) as has_invalid
      from words_check_words(....)
     group by word
)
select coalesce(sum(score), 0),
       bool_or(has_invalid)
INTO STRICT
    total_user_score,
    invalid_words_found
FROM cte;

IF invalid_words_found THEN
    RAISE EXCEPTION "Invalid words found";
ELSE
    UPDATE games SET user_score = user_score + total_user_score;
END IF;
Comments