Oracle数据库employee_1表查询：Mobile_No与Sim_NO相同时取最大Start_Date

Hey there! Let's figure out how to solve this Oracle query issue you're stuck on. You need to pull records where Mobile_No and Sim_NO are the same, and for each matching pair, get the one with the latest (maximum) Start_Date—plus you want exactly 2 target records in the result. Here are two reliable approaches you can test out:

This method first identifies the latest start date for each valid (Mobile_No, Sim_NO) pair, then joins that result back to the original table to get the full record details:

SELECT e.*
FROM employee_1 e
INNER JOIN (
    -- Get the max Start_Date for each matching Mobile_No/Sim_NO pair
    SELECT Mobile_No, Sim_NO, MAX(Start_Date) AS latest_start
    FROM employee_1
    WHERE Mobile_No = Sim_NO
    GROUP BY Mobile_No, Sim_NO
) grouped_pairs 
    ON e.Mobile_No = grouped_pairs.Mobile_No
    AND e.Sim_NO = grouped_pairs.Sim_NO
    AND e.Start_Date = grouped_pairs.latest_start
-- Limit to exactly 2 records
FETCH FIRST 2 ROWS ONLY;

• The subquery narrows down to rows where Mobile_No equals Sim_NO, then groups those pairs to find the most recent start date for each.
• Joining back to employee_1 lets us retrieve all the fields (like End_Date) for those latest-date records.
• If you want predictable ordering for the 2 records (e.g., show the most recent overall first), add an ORDER BY Start_Date DESC right before FETCH FIRST 2 ROWS ONLY.

Window functions are perfect for "top N per group" scenarios like this. We'll rank records within each matching pair group by their start date, then pick the top-ranked (latest) record per group:

WITH ranked_records AS (
    SELECT 
        *,
        -- Assign rank: 1 = latest Start_Date in each Mobile_No/Sim_NO group
        ROW_NUMBER() OVER (
            PARTITION BY Mobile_No, Sim_NO 
            ORDER BY Start_Date DESC
        ) AS record_rank
    FROM employee_1
    WHERE Mobile_No = Sim_NO
)
SELECT Mobile_No, Sim_NO, Start_Date, End_Date
FROM ranked_records
WHERE record_rank = 1 -- Only keep the latest record per group
FETCH FIRST 2 ROWS ONLY;

• The CTE (ranked_records) adds a record_rank column: for each group of identical Mobile_No and Sim_NO, the record with the newest Start_Date gets rank 1.
• Filtering for record_rank = 1 gives us exactly the latest entry per valid pair.
• Again, add an ORDER BY clause if you need control over which 2 records are returned (e.g., ORDER BY Mobile_No to sort by phone number).

If you were getting unexpected results before, double-check that your WHERE Mobile_No = Sim_NO condition is correctly filtering only the pairs you care about, and that there are at least 2 distinct (Mobile_No, Sim_NO) pairs in your table (otherwise you won't get 2 records back).

内容的提问来源于stack exchange，提问作者Mayur