如何获取各Matter Number的最新记录并优化Extranet表单视图？

Hey there! Let's tackle this problem—you need to display only the most recent entry for each Matter Number in your Extranet form view, ditching those older historical records (like the ones marked red on the right). Here are practical solutions depending on where you're handling your data logic:

1. Database-Level Filtering (Most Common Approach) #

If your data comes from a database, using window functions or a subquery is the most efficient way to get only the latest records.

Using Window Functions (Modern SQL Databases: PostgreSQL, SQL Server, MySQL 8+, etc.) #

This method uses ROW_NUMBER() to rank records per Matter Number, keeping only the top-ranked (latest) one:

WITH ranked_matter_records AS (
    SELECT 
        *,
        -- Rank records in each Matter Number group by Event Date (newest first)
        ROW_NUMBER() OVER (
            PARTITION BY [Matter Number] 
            ORDER BY [Event Date] DESC, [Record ID] DESC -- Add Record ID to handle ties
        ) AS record_rank
    FROM your_table_name
)
SELECT *
FROM ranked_matter_records
WHERE record_rank = 1;

• PARTITION BY [Matter Number] groups records by each unique Matter Number.
• ORDER BY [Event Date] DESC ensures the newest date gets rank 1; adding [Record ID] DESC handles cases where multiple records have the same latest date (picks the most recently created one).

For Older SQL Databases (No Window Function Support) #

If you're working with an older database that doesn't support window functions, use a subquery to first find the latest date per Matter Number, then join back to get the full record:

SELECT main.*
FROM your_table_name main
INNER JOIN (
    -- Get the latest Event Date for each Matter Number
    SELECT [Matter Number], MAX([Event Date]) AS latest_event_date
    FROM your_table_name
    GROUP BY [Matter Number]
) latest_dates 
    ON main.[Matter Number] = latest_dates.[Matter Number] 
    AND main.[Event Date] = latest_dates.latest_event_date;

Note: If multiple records share the same latest date for a Matter Number, this will return all of them. Add an extra condition (like matching the highest Record ID) if you need a single unique record.

2. Frontend-Level Filtering (If Data is Already Loaded) #

If you're handling the data in your frontend view (e.g., JavaScript for a web form), you can process the array of records to keep only the latest entry per Matter Number:

// Sample input data (replace with your actual dataset)
const matterRecords = [
    { matterNumber: 'MAT-1001', eventDate: '2024-06-10', otherField: 'Value A' },
    { matterNumber: 'MAT-1001', eventDate: '2024-06-05', otherField: 'Value B' },
    { matterNumber: 'MAT-1002', eventDate: '2024-06-08', otherField: 'Value C' }
];

// Step 1: Sort records by Event Date (newest first)
const sortedRecords = matterRecords.sort((a, b) => 
    new Date(b.eventDate) - new Date(a.eventDate)
);

// Step 2: Keep only the first entry for each Matter Number
const latestMatterRecords = Object.values(
    sortedRecords.reduce((acc, currentRecord) => {
        // Only add the record if we haven't already stored one for this Matter Number
        if (!acc[currentRecord.matterNumber]) {
            acc[currentRecord.matterNumber] = currentRecord;
        }
        return acc;
    }, {})
);

// Now latestMatterRecords contains only the newest entry per Matter Number
console.log(latestMatterRecords);

This works by first sorting all records so the newest comes first, then using reduce to build an object where each key is a Matter Number, storing only the first (latest) record encountered.

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