Postgresql – How to get the minimum sum value of fields without repeating where clause

postgresql

How can I get the minimum value after adding value of 2 fields without repeating the where clause?

Example: Get records with minimum value of num1 + num2 and deleted_at is null

id          num1        num2       deleted_at
----------  ---------   ---------  ---------  
1           1           1          2015-01-01
2           2           2          null
3           2           2          null         
4           4           4          null

Expected result is

id          num1        num2       deleted_at
----------  ---------   ---------  ---------
2           2           2          null   
3           2           2          null

I tried subquery, but I have to repeat the where clause

SELECT * 
FROM   foos t1 
WHERE  t1.num1 + t1.num2 = (SELECT Min(t2.num1 + t2.num2) 
                            FROM   foos t2 
                            WHERE  t2.deleted_at IS NULL) //repeat
       AND t1.deleted_at IS NULL //repeat

Best Answer

In Sql Server , you can do it with RANK

CREATE TABLE foos
    ([id] int, [num1] int, [num2] int, [deleted_at] datetime)
;

INSERT INTO foos
    ([id], [num1], [num2], [deleted_at])
VALUES
    (1, 1, 1, '2015-01-01'),
    (2, 2, 2, NULL),
    (3, 2, 2, NULL),
    (4, 4, 4, NULL)
;

--the select

SELECT [id], [num1], [num2]
FROM 
(
 SELECT [id], [num1], [num2]
  , RANK() OVER(ORDER BY [num1]+ [num2] ASC) as rk
 FROM foos
 WHERE [deleted_at] IS NULL
)as r
WHERE r.rk = 1

output:

id  num1    num2
2      2    2
3      2    2

dbfiddle here

Related Solutions

PostgreSQL – Using Variable to Determine Column Name in SELECT

I think you might be looking for the USING clause:

EXECUTE format('SELECT COALESCE(%I,col,$1) FROM public.TableMix 
                WHERE name = $2', ruleName)
        USING (username, whateverTheSecondParameterIs);

You use $1, $2, etc for placement-parameters, passed by USING. You use %I for itentifiers expanded by format.

Update: If you want to coalesce the column name ruleName if it's null, perhaps you want something more like:

EXECUTE format('SELECT %I FROM public.TableMix 
                WHERE name = $1', coalesce(ruleName, 'col'))
        USING (username);

You need to think about order of evaluation. What's part of the generated SQL, and what's evaluated in order to create that SQL?

PostgreSQL – ON DELETE Rule Not Working with WHERE Clause

The manual in Rules on INSERT, UPDATE, and DELETE) describes the INSTEAD mechanism for the context of your rule as:

Qualification given and INSTEAD

the query tree from the rule action with the rule qualification an the original query tree's qualification; and the original query tree with the negated rule qualification added

Overlooking the part emphasized above would be the cause of the unexpected result. I believe that in your test case, the mentioned DELETE will be transformed by the rule into commands to the same effect as:

UPDATE categories SET deleted_at = NOW()
    WHERE categories.id = categories.id AND deleted_at IS NULL;

DELETE from categories WHERE NOT old.deleted_at IS NULL;

The DELETE removes the row because the UPDATE before it has just set its deleted_at field to non-null.

Note also the categories.id = categories.id condition which is obviously not needed and comes from the misunderstanding that OLD does not reference a specific row like in a trigger, it's meant to be replaced by the table subject to the rule.

The main point to understand is that rules don't look themselves at the rows and so they don't generate a kind of IF-THEN-ELSE construct depending on the row contents. The WHERE clauses of the RULES (the qualifications) are not evaluated by the rule system, they are injected into the commands produced by the rule system and will be evaluated by the commands themselves.

See also What are PostgreSQL RULEs good for? and links within for some interesting insights on rules and why they're hard to use.

Best Answer

Related Solutions

PostgreSQL – Using Variable to Determine Column Name in SELECT

PostgreSQL – ON DELETE Rule Not Working with WHERE Clause

Related Question