Last entry on date for 2nd fields



  • I have the following table to get the tapes marked +:

    -------------------------------
    FID | SID | DATE | VAL

    1 | 1 | 1997-08-12 | 'qwe' +
    1 | 1 | 1997-07-14 | 'asd'
    1 | 1 | 1997-06-15 | 'asd'
    1 | 2 | 1997-08-06 | 'qwe' +
    1 | 2 | 1997-07-11 | 'zxv'

    So the last FID and SID records on the date. MSSQL.



  • Use the window function. row_number()♪ Start the window. partition by FID, SIDin which the lines are renumbered as specified order by Date desc♪ Then remove all lines with number one inside your window:

    ;with vTableName as (
        select
            rn = row_number()
                over (
                    partition by FID, SID
                    order by [Date] desc)
            , *
        from TableName
    )
    select *
    from vTableName
    where rn = 1;
    

    Compared to type requests

    select *
    from <Таблица>
    where not exists(select 1 from <Эта же таблица> where ...)
    

    This approach reduces the number of IOs of operations, as the reference table is involved in the request One One.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2