Geek asks and answers

Choose recent entries per person

I'm just wondering if the following two sql statements have any performance differences or if they are essentially the same:

To select the last record, the spouse of the person from the table spouse(person_id, spouse_id, marriage_date).

select *
from spouse 
where (person_id, marriage_date) in ( select person_id, max(marriage_date) 
                                      from spouse  
                                      group by person_id
                                     )


select *
from spouse s1
where marriage_date = ( select max(marriage_date) 
                        from spouse s2  
                        where s1.person_id = s2.person_id  
                      )

This is a common reporting requirement, for example, recent employee work, higher education, etc. etc. I would like to know if you prefer the above statements one by one and why, or if there are others better (in terms of performance / readability) to fulfill these requirements for the most recent / maximum.

+3
source share
3 answers

, SQL, .

SELECT s1.person_id, s1.marriage_date
FROM spouse s1
JOIN ( 
    SELECT 
        person_id, 
        ROW_NUMBER() OVER (PARTITION BY person_id ORDER BY marriage_date DESC) AS Priority
        FROM spouse
    ) s2
ON s2.person_id = s1.person_id AND s2.Priority = 1

person_id, (ROW_NUMBER()), marriage_date. person_id, s2.Priority = 1 , max marriage_date .

:

+3

:

SELECT *
  FROM (SELECT ROW_NUMBER() OVER (PARTITION BY person_id
                                   ORDER BY marriage_date DESC) AS r,
               t.*
          FROM spouse t) x
 WHERE x.r = 1

, .

+1

Your examples are syntactically the same. Typically, use IN when the subquery is the most restrictive (i.e., Eliminates many records from the result set) and use the correlated subquery when the outer query is the most restrictive (i.e., there are more records in the subquery than the general result set) .

Based on table statistics and estimated cost, the Oracle query optimizer can rewrite the IN clause into a correlated subquery and vice versa. Look at the plans to explain your situation and choose the plan with the lowest cost.

0
source

More articles:


All Articles