Is this correct to use DISTINCT instead of GROUP BY?

Question

Example data:

id | docn | item | suma
---------------------
1     33     x   |   10
1     33     y   |   20
2     37     a   |   10
2     37     b   |   20
2     37     c   |   30

To group results I can write:

SELECT sum( suma ),  
  (ocd.o).* 
FROM order_cost_details() ocd
where (ocd.o).id IN ( 6154, 10805 )
GROUP BY ocd.o

But in a place with a group I want to select last_value for each group. Next does not work:

SELECT sum( suma ),  
  (ocd.o).*, 
  last_value( ocd.c ) OVER (PARTITION BY ocd.o ) 
FROM order_cost_details() ocd
where (ocd.o).id IN ( 6154, 10805 )
GROUP BY ocd.o

SQL Error [42803]: ERROR: column "ocd.c" must appear in the GROUP BY clause or be used in an aggregate function

I rewrite my query like next:

SELECT DISTINCT sum( suma ) OVER ( PARTITION BY ocd.o ),  
  (ocd.o).*, 
  last_value( ocd.c ) OVER (PARTITION BY ocd.o ) 
FROM order_cost_details() ocd
where (ocd.o).id IN ( 6154, 10805 )

Results seems expected: with correct last_value:

But I am not sure is this correct to use DISTINCT instead of GROUP BY here?

Add sample data and expected output as plain-text only and not images. — Kaushik Nayak, Jul 11 '19 at 09:29
It really not clear to me what you want to do because your examples are quite confusing. But you should always consider: last_value() works someway different than you would expect: https://stackoverflow.com/questions/42299101/window-functions-last-valueorder-by-asc-same-as-last-valueorder-by-d You should use first_value with DESC order instead — S-Man, Jul 11 '19 at 09:30
@KaushikNayak: I just want to select other aggregated info for the `Order`. When writing old school queries I need `GROUP BY`, but window functions does not work with `GROUP BY`. Here I just ask about how to write such queries. Example data added — Eugen Konkov, Jul 11 '19 at 11:39

score 2 · Answer 1 · answered Jul 11 '19 at 09:33

2

last_value() often does not work as expected Window Functions: last_value(ORDER BY ... ASC) same as last_value(ORDER BY ... DESC)

To get the last value of a partition, a more valid way is getting the first value of the descending order:

SELECT
    first_value(my_column) OVER (PARTITION BY partitioned_column ORDER BY order_column DESC)
FROM
    ...

answered Jul 11 '19 at 09:33

S-Man

22,521
7
40
63

Very useful hint. Thank you. – Eugen Konkov Jul 11 '19 at 11:34
found this solution: https://stackoverflow.com/a/67167595/4632019 – Eugen Konkov May 13 '21 at 13:08

score 0 · Answer 2 · answered Jul 11 '19 at 16:29

You can use a subselect:

SELECT sum(suma),  
       (o).*,
       last_c
FROM (SELECT suma,
             ocd.o
             last_value(ocd.c)
                OVER (PARTITION BY ocd.o
                      ORDER BY some_col)
                AS last_c 
      FROM order_cost_details() ocd
      where (ocd.o).id IN (6154, 10805)
     ) AS q
GROUP BY o, last_c;

Eugen Konkov · Answer 3 · 2019-07-12T11:38:37.087

From the IRC

RhodiumToad: using DISTINCT ON is almost always a mistake (do bear in mind it's completely non-standard)

The basic rule of thumb is that you use GROUP BY when you want to reduce the number of output rows, and window functions when you want to keep the number of rows the same

Nothing stops you doing a total over the orders using a window function after the group by

Thus I rewrite my query to look like:

 SELECT *,
   sum( t.group_suma     ) OVER( PARTITION BY (t.o).id ) AS total_suma
 FROM (
    SELECT 
     sum( ocd.item_cost     ) AS group_cost,
     sum( ocd.item_suma     ) AS group_suma,
     max( (ocd.ic).consumed ) AS consumed,
     ocd.o
    FROM order_cost_details() ocd
    where (ocd.o).id IN ( 6154, 10805 )
    GROUP BY ocd.o, (ocd.ic).consumed_period
 ) t

Is this correct to use DISTINCT instead of GROUP BY?

3 Answers3