Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2013
    Posts
    1

    Unanswered: How to select latest row when ID has one or more rows and each ID unique frm & to dte

    I have a equipment table and the equipment has a coding for each place / location or custody it has had during its life. I need to select the latest (newest) row for each piece of equipment by getting the newest from_date and to_date field combination.

    The following is an example. I know how to get MAX date for one column but not with two columns (from and to DATES). Any suggestions or examples would be great. I know with other db languages there are GREATEST functions. Not able yet to find the options.


    ID

    NAME

    FROM_DATE

    TO_DATE


    21

    TNK101

    01/01/2013

    03/15/2013


    21

    TNK101

    03/16/2013

    05/05/2013


    21

    TNK101

    05/06/2013

    12/31/2075


    34

    BNK201

    03/15/2013

    12/31/2075


    104

    CRL309

    01/01/2013

    03/15/2013


    104

    CRL309

    03/01/2013

    05/25/2013


    104

    CRL309

    05/26/2013

    11/15/2013


    104

    CRL309

    11/16/2013

    12/31/2075

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    One solution would be:
    Code:
    DECLARE @t TABLE (
       ID           INT             NOT NULL
    ,  NAME         NVARCHAR(20)    NOT NULL
    ,  FROM_DATE    DATE            NOT NULL
    ,  TO_DATE      DATE            NOT NULL
       );
    
    INSERT INTO @t VALUES
       ( 21, 'TNK101', '01/01/2013', '03/15/2013')
    ,  ( 21, 'TNK101', '03/16/2013', '05/05/2013')
    ,  ( 21, 'TNK101', '05/06/2013', '12/31/2075')
    ,  ( 34, 'BNK201', '03/15/2013', '12/31/2075')
    ,  (104, 'CRL309', '01/01/2013', '03/15/2013')
    ,  (104, 'CRL309', '03/01/2013', '05/25/2013')
    ,  (104, 'CRL309', '05/26/2013', '11/15/2013')
    ,  (104, 'CRL309', '11/16/2013', '12/31/2075');
    
    WITH cte AS (
       SELECT ID, NAME, FROM_DATE, TO_DATE
    ,  Row_Number() OVER (PARTITION BY ID ORDER BY ID, FROM_DATE) AS trick
       FROM @t )
    SELECT ID, NAME, FROM_DATE, TO_DATE
       FROM cte
       WHERE 1 = trick;
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •