Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2008
    Posts
    94

    Unanswered: To pick the latest record based on other columns?.

    Hi i need a help to achieve this requirement.

    Code:
    COL1  COL2  COL3  COL4
                                                                          
    10      1000  3333   2011-10-10 05:00:44 AM 000000
    11      1000  3333   2011-10-10 05:00:48 AM 000000
    12      2000  4444   2011-10-10 05:00:48 AM 000000
    12      2000  4444   2011-10-10 05:00:48 AM 000000
    14      3000  5555   2011-10-10 05:00:26 AM 000000
    13      3000  5555   2011-10-10 05:00:48 AM 000000
    15      4000  6666   2011-10-10 05:00:53 AM 000000
    16      5000  7777   2011-10-10 05:00:50 AM 000000
    17      5000  7777   2011-10-10 05:00:50 AM 000000
    
    Output
    
    COL1  COL2  COL3  COL4
                                                                          
    11      1000  3333   2011-10-10 05:00:48 AM 000000
    12      2000  4444   2011-10-10 05:00:48 AM 000000
    12      2000  4444   2011-10-10 05:00:48 AM 000000
    13      3000  5555   2011-10-10 05:00:48 AM 000000
    15      4000  6666   2011-10-10 05:00:53 AM 000000
    16      5000  7777   2011-10-10 05:00:50 AM 000000
    if col2 and col3 is same and col1 is different then retain latest by using timestamp(col4). i have tried the below query. but im not getting the expected result.


    Code:
    select * from
    (select col1,
    col2,
    col3,
    col4,
    row_number() over(partition by col2,col3 order by col4 desc) as dup
    from 
    table1 ) a
    where
    a.dup=1

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Nov 2011
    Posts
    334
    plz try this
    Code:
    SELECT COL1,COL2,COL3,COL4
    FROM   (SELECT COL1,
                   COL2,
                   COL3,
                   COL4,
                   ROW_NUMBER()
                     OVER(PARTITION BY COL2,COL3 ORDER BY COL4 DESC) AS DUP
                   ,count(0)
                     over(partition by col1,col2,col3) as cnt
            FROM   T12) A
    WHERE  A.DUP = 1
    OR     A.CNT > 1;

  4. #4
    Join Date
    Nov 2011
    Posts
    334
    The query above needs to do table sort twice.
    and we can use this query to eliminate one sort
    Code:
    SELECT COL1,COL2,COL3,COL4
    FROM   (SELECT COL1,
                   COL2,
                   COL3,
                   COL4,
                   ROW_NUMBER()
                     OVER(PARTITION BY COL2,COL3 ORDER BY COL4 DESC) AS DUP
                   ,min(COL1)
                     over(partition by col2,col3) as minc1
                   ,max(COL1)
                     over(partition by col2,col3) as maxc1
            FROM   T12) A
    WHERE  A.DUP = 1
    OR     A.MAXC1 = A.MINC1;

Posting Permissions

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