sqlsql-server

Select the first record of a query with an in condition


I have a table with information like this:

COD_FICHERO,FECHA,ID_VERSION,CODIGO_FRONTERA,FACTOR_PERDIDAS
TFROC,2023-10-23 00:00:00.000,2,Frt5125,1.32993586654688
TFROC,2023-10-04 00:00:00.000,2,Frt0054,1.1361400284643
TFROC,2023-10-09 00:00:00.000,2,Frt4917,1.13871246571916
TFROC,2023-10-03 00:00:00.000,2,Frt2604,1.44391785864422
TFROC,2023-10-27 00:00:00.000,2,Frt2007,1.21119103377573

The columns that interest me are CODIGO_FRONTERA and FACTOR_PERDIDAS. For the same CODIGO_FRONTERA there are several records since there are many dates, however I am only interested in the last date. Then, I naively thought this would solve my problem:

SELECT TOP(1) CODIGO_FRONTERA, FACTOR_PERDIDAS
FROM analiticaitsqldw.app.tyd_bdmen_tfroc tba
WHERE CODIGO_FRONTERA IN ('FRT49161',
    'Frt00270',
    'Frt00338',
    'Frt45847',
    'Frt46655')
AND ID_VERSION = '2'
ORDER BY FECHA DESC;

But as is obvious, I only bring a single record but not the last record for each "BORDER_CODE". How can I achieve this efficiently?


Solution

  • To retrieve the last record for each "BORDER_CODE," you can use a common table expression (CTE) with the ROW_NUMBER window function to rank the rows based on the FECHA column in descending order for each "BORDER_CODE." Then, you can filter the results to select the records with a row number of 1 for each "BORDER_CODE." Here's the SQL query to achieve this efficiently:

    WITH RankedRecords AS (
      SELECT
        CODIGO_FRONTERA,
        FACTOR_PERDIDAS,
        ROW_NUMBER() OVER (PARTITION BY CODIGO_FRONTERA ORDER BY FECHA DESC) AS rn
      FROM analiticaitsqldw.app.tyd_bdmen_tfroc tba
      WHERE CODIGO_FRONTERA IN ('FRT49161', 'Frt00270', 'Frt00338', 'Frt45847', 'Frt46655')
        AND ID_VERSION = '2'
    )
    
    SELECT CODIGO_FRONTERA, FACTOR_PERDIDAS
    FROM RankedRecords
    WHERE rn = 1;
    

    In this query:

    1. The ROW_NUMBER window function is used to assign a row number to each row within each "BORDER_CODE" group based on the FECHA column in descending order (ORDER BY FECHA DESC). This creates a ranked list of records for each "BORDER_CODE."

    2. The PARTITION BY clause in the ROW_NUMBER function ensures that the ranking is done separately for each "BORDER_CODE."

    3. The CTE RankedRecords contains the ranked records.

    4. In the final SELECT statement, you filter the records with a row number equal to 1 for each "BORDER_CODE," which represents the last record for each group.

    This query efficiently retrieves the last record for each "BORDER_CODE" based on the specified criteria.