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?

Answer 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;