Select row A if a condition satisfies else select row B for each group-CodePudding

We have 2 tables, bookings and docs

bookings

booking_id    |  name    
100           |  "Val1"  
101           |  "Val5"  
102           |  "Val6"

docs

doc_id  |  booking_id  |  doc_type_id  
6       |  100         | 1
7       |  100         | 2
8       |  101         | 1
9       |  101         | 2
10      |  101         | 2

We need the result like this:

booking_id | doc_id
100        | 7
101        | 10

Essentially, we are trying to get the latest record of doc per booking, but if doc_type_id 2 is present, select the latest record of doc type 2 else select latest record of doc_type_id 1.

Is this possible to achieve with a performance friendly query as we need to apply this in a very huge query?

CodePudding user response：

SELECT booking_id, doc_id
RANK() OVER (  
    PARTITION BY doc_id
    ORDER BY doc_id DESC)  
AS 'my_rank' FROM docs;

CodePudding user response：

You can do it with FIRST_VALUE() window function by sorting properly the rows for each booking_id so that the rows with doc_type_id = 2 are returned first:

SELECT DISTINCT booking_id,  
       FIRST_VALUE(doc_id) OVER (PARTITION BY booking_id ORDER BY doc_type_id = 2 DESC, doc_id DESC) rn
FROM docs;

If you want full rows returned then you could use ROW_NUMBER() window function:

SELECT booking_id, doc_id, doc_type_id  
FROM (
  SELECT *, 
         ROW_NUMBER() OVER (PARTITION BY booking_id ORDER BY doc_type_id = 2 DESC, doc_id DESC) rn
  FROM docs
) t
WHERE rn = 1;