How to select TOP 1 B for each A in an Oracle SQL join query?

Question

Here is a common structure I use in SQL Server that allows me, for example, to select a specific service record in a SERVICE table for each client in a CLIENT table. In this scenario, a client record can have 0, 1 or many service records. I want the most recent service record for each client (according to the start date of the service). And if a client doesn't have a service record, I still want the client's record in the result set (with NULLs to be displayed in the service record fields).

SELECT 
  A.client_id,
  A.client_name,
  A.client_city,
  B.service_id,
  B.service_type,
  B.service_start_date,
  B.service_end_date
FROM
  client AS A
LEFT JOIN
  service AS B
ON
  B.service_id = (SELECT TOP 1 X.service_id FROM service AS X
                         WHERE X.client_id = A.client_id
                         ORDER BY X.service_start_date DESC)

I have tried several variants of the solutions I've found on-line, but nothing appears to work for me

you need to edit your question, show all table DDL, sample data, and expected outputs. thanks — OldProgrammer, Dec 19 '22 at 21:43
One quirk of Oracle is that it does not allow the keyword "AS" to be used when assigning an alias for a table, although "AS" is allowed for column aliases. Thus, you must say e.g. "...FROM MYTABLE X..." instead of "...FROM MYTABLE AS X...". The latter will result in a typically unhelpful Oracle error message such as "SQL command not properly ended", or "missing right parenthesis" if you've put the offending "AS" in a subquery. — Bob Jarvis - Слава Україні, Dec 19 '22 at 22:22

score 3 · Answer 1 · answered Dec 19 '22 at 21:45

3

You use a row_number() windowing function to know which one is first for each client and then just use a left join to get the data.

Like this:

SELECT 
  A.client_id,
  A.client_name,
  A.client_city,
  B.service_id,
  B.service_type,
  B.service_start_date,
  B.service_end_date
FROM client AS A
LEFT JOIN (
  SELECT service_id, service_type, service_start_date, service_end_date,
         ROW_NUMBER(PARTITION BY client_id ORDER BY service_start_date DESC) AS RN 
  FROM service
) B ON A.client_id = B.client_id AND B.RN = 1

answered Dec 19 '22 at 21:45

Hogan

69,564
10
76
117

1

This approach should even work correctly on each or at least the most DBMS. – Jonas Metzler Dec 19 '22 at 21:50
I believe I've done similar in MS SQL. – Tiny Haitian Dec 19 '22 at 22:03
Excellent. Thank you, I'll be using this technique extensively. – Ron Kelly Dec 19 '22 at 22:38

score 0 · Answer 2 · answered Dec 19 '22 at 22:03

If there's an index on service (client_id, service_start_date) then a lateral query can be very performant.

For example:

SELECT 
  A.client_id,
  A.client_name,
  A.client_city,
  B.service_id,
  B.service_type,
  B.service_start_date,
  B.service_end_date
FROM client A
OUTER APPLY (
  SELECT X.service_id 
  FROM service AS X
  WHERE X.client_id = A.client_id
  ORDER BY X.service_start_date DESC
  FETCH FIRST 1 ROWS ONLY
) B

How to select TOP 1 B for each A in an Oracle SQL join query?

2 Answers2