How to group a sum query without using group by

Question

I have a large query/table with many columns, and the primary key in the query/table is the id_shift. Multiple orders belong to one shift, and for each shift I want to display the value of the order with the largest ldm value (length of shipment).

I do not want to use group by because then I would need to specify all the columns in the query (which are about 50-100 columns), and it is important that the query is fast.

I have created this query (and I want to add it to the large query):

SELECT
    (MAX(ldm.uvalue) OVER ()) AS [max ldm],
    plannedshift.id_shift

FROM plannedshift
    LEFT JOIN action ac ON plannedshift.id_shift=ac.id_shift AND ac.name = 'pickup'
    JOIN [order] ord ON ac.id_order = ord.id_order AND ac.name = 'pickup' 
    LEFT JOIN orderamount ldm ON ord.id_order = ldm.id_order AND ldm.id_unit = 5

But this gives me multiple rows for the same id_shift, because a row is created for each order. For example:

id_shift	max ldm
62822	12.80
62822	12.80
62822	12.80

Is there something I can do to get only one row for each id_shift, with the max ldm value from all the orders that belong to that shift?

*"Is there something I can do to get only one row for each id_shift"* Yes, add a `GROUP BY` and not a windowed aggregate; the thing you say you explicitly don't want to do. Then only 1 row for each distinct group would be returned. Writing `GROUP BY id_shift` isn't a lot. — Thom A, Aug 31 '21 at 10:07
Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) — Charlieface, Aug 31 '21 at 11:13

score 0 · Answer 1 · answered Aug 31 '21 at 10:30

You can use row_number():

SELECT ps.*
FROM (SELECT . . .,   -- whatever columns you want
             ROW_NUMBER() OVER (PARTITION BY ps.id_shift ORDER BY ldm.uvalue DESC) as seqnum
      FROM plannedshift ps LEFT JOIN
           action ac
           ON ps.id_shift = ac.id_shift AND
              ac.name = 'pickup' LEFT JOIN
           [order] ord
           ON ac.id_order = ord.id_order AND
              ac.name = 'pickup' LEFT JOIN
           orderamount ldm
           ON ord.id_order = ldm.id_order AND
              ldm.id_unit = 5
      ) ps
WHERE seqnum = 1;

Note that I changed all the JOINs to LEFT JOINs to ensure that all shifts from the first table are in the result set.

score 0 · Answer 2 · answered Aug 31 '21 at 10:47

You only need to include the columns you are actually displaying in a group by.

If you're query above is the complete query then this is not difficult at all:

group by ps.id_shift

Otherwise you could try a distinct after the select:

SELECT distinct
    (MAX(ldm.uvalue) OVER ()) AS [max ldm],
    plannedshift.id_shift

FROM plannedshift...

score 0 · Answer 3 · answered Aug 31 '21 at 10:51

You can use a TOP (1) query that you add with OUTER APPLY to your main query in order to get the top row per shift:

select ...
from ...
outer apply
(
  SELECT TOP(1) *
  FROM action ac
  JOIN orderamount ldm ON ldm.id_order = ac.id_order AND ldm.id_unit = 5 
  WHERE ac.id_shift = plannedshift.id_shift AND ac.name = 'pickup'
  ORDER BY ldm.uvalue DESC
) top_order

How to group a sum query without using group by

3 Answers3