Group By with fewer fields than the select

Question

I have two Oracle tables:

USER(ID*,NAME,SURNAME)
MATCH(ID*,START_DATE,END_DATE,MATCH_CODE,ID_USER**)

I need a query to get for each USER the match with the maximum difference in seconds between END_DATE and START_DATE and in addition the NAME and MATCH_CODE fields.

My query:

SELECT A.ID,A.NAME,MAX(extract(second from (END_DATE-START_DATE))
                     + extract(minute from (END_DATE-START_DATE)*60
                     + extract(hour from (END_DATE-START_DATE)*60*60
                     + extract(day from (END_DATE-START_DATE)*60*60*24) max_differance
FROM USER A JOIN MATCH B
ON A.ID = B.ID_USER
GROUP BY A.ID;

I was thinking about this query but obviously it gives an error because in the GROUP BY all the fields of the select go. Also I would need the MATCH_CODE field, how should I do?

So you have multiple rows for each (or some) `ID` with different values for `NAME`? Which of those do you want to select? And if you don't you can just add `NAME` to the `GROUP BY` — HoneyBadger, Nov 04 '21 at 15:22
Probably a duplicate of https://stackoverflow.com/questions/121387/ or https://stackoverflow.com/questions/16529701/ — MT0, Nov 04 '21 at 15:57
i need to get for each USER: id, name, match_code, and max (end_date - start_date). So in the result there will be only one record for each USER and the associated values are those relative to the maximum difference — Fesilox, Nov 04 '21 at 16:34

score 1 · Accepted Answer · answered Nov 04 '21 at 15:49

1

Aggregate the name column and use MAX ... KEEP to get the match_code:

SELECT u.id,
       MAX(u.name) AS name,
       MAX(end_date - start_date)*24*60*60 AS max_difference,
       MAX(match_code) KEEP (
         DENSE_RANK LAST
         ORDER BY end_date - start_date NULLS FIRST
       ) As match_code
FROM   "USER" u
       INNER JOIN match m
       ON (u.id = m.id_user)
GROUP BY u.id

Or, use analytic functions:

SELECT id,
       name,
       max_difference,
       match_code
FROM   (
  SELECT u.id,
         u.name,
         (end_date - start_date)*24*60*60 AS max_difference,
         match_code,
         ROW_NUMBER() OVER (PARTITION BY u.id ORDER BY end_date - start_date DESC)
           AS rn
  FROM   "USER" u
         INNER JOIN match m
         ON (u.id = m.id_user)
)
WHERE  rn = 1;

answered Nov 04 '21 at 15:49

MT0

143,790
11
59
117

I tested the first solution and it seems to work, I didn't understand what MAX (match_code) KEEP ( DENSE_RANK LAST ORDER BY end_date - start_date NULLS FIRST). the match_code obtained is surely associated with the correct USER? – Fesilox Nov 04 '21 at 16:37
If you just use `MAX(match_code)` then you would get the last `match_code` (probably alphabetically). What you want to do it to get the `match_code` associated with the correct `max_difference` and to do that you need to just `KEEP` only the rows with with the greatest difference (i.e. those rows `LAST` in the `ORDER`) and take the `MAX` of those rows. – MT0 Nov 04 '21 at 16:51

Group By with fewer fields than the select

1 Answers1