Get the Recent Record

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • pradkumar
    New Member
    • May 2007
    • 1

    Get the Recent Record

    Hi All

    I am having the a table(1 million) with data as follows:

    Input:
    Col1 Col2 Col3
    1 1895 4/7/2007
    2 1895 4/7/2007
    3 1895 3/5/2005
    4 6600 3/3/2007
    5 6600 3/3/2007
    6 6600 3/1/2006
    so on


    Now I would like to have my output as follows:

    Desired Output:
    Col1 Col2 Col3
    1 1895 4/7/2007
    4 6600 3/3/2007

    I tried using the following query for one id.

    select * from T1 A
    where A.Col3=(SELECT MAX(C.Col3) FROM T1 C
    WHERE A.Col2=C.Col2)
    and A.Col2=1895
    AND ROWNUM<2

    This worked fine and I got the output for 1895

    When I tried using this
    select * from T1 A
    where A.Col3=(SELECT MAX(C.Col3) FROM T1 C
    WHERE A.Col2=C.Col2)
    AND ROWNUM<2

    on bulk data, it is giving only one record. I assume this is due to rownum<2.
    Please share your ideas how can i apply that to a full table
  • frozenmist
    Recognized Expert New Member
    • May 2007
    • 179

    #2
    Hi ,
    What does the first column signify?
    If you want the first record of each value of col2 then try this code

    Code:
    select a.col2,a.col3 from (select col2,col3,rownum as rn from T1) a where (a.col2,a.rn) IN (select col2,min(rownum) from T1 group by col2)
    Hope it helps
    Cheers

    Comment

    • gangadharampidugu
      New Member
      • May 2007
      • 5

      #3
      Originally posted by pradkumar
      Hi All

      I am having the a table(1 million) with data as follows:

      Input:
      Col1 Col2 Col3
      1 1895 4/7/2007
      2 1895 4/7/2007
      3 1895 3/5/2005
      4 6600 3/3/2007
      5 6600 3/3/2007
      6 6600 3/1/2006
      so on


      Now I would like to have my output as follows:

      Desired Output:
      Col1 Col2 Col3
      1 1895 4/7/2007
      4 6600 3/3/2007

      I tried using the following query for one id.

      select * from T1 A
      where A.Col3=(SELECT MAX(C.Col3) FROM T1 C
      WHERE A.Col2=C.Col2)
      and A.Col2=1895
      AND ROWNUM<2

      This worked fine and I got the output for 1895

      When I tried using this
      select * from T1 A
      where A.Col3=(SELECT MAX(C.Col3) FROM T1 C
      WHERE A.Col2=C.Col2)
      AND ROWNUM<2

      on bulk data, it is giving only one record. I assume this is due to rownum<2.
      Please share your ideas how can i apply that to a full table
      Hi,
      Try this one once
      select col1,col2,col3 from t1 where col3 in (select max(col3) from t1 group by col2)

      hope it may help u.

      Comment

      • frozenmist
        Recognized Expert New Member
        • May 2007
        • 179

        #4
        Originally posted by gangadharampidu gu
        Hi,
        Try this one once
        select col1,col2,col3 from t1 where col3 in (select max(col3) from t1 group by col2)

        hope it may help u.
        Hi gangadhar,
        If the requirement is as understood by you the shouldn't your query be like
        [code=sql]
        select col1,col2,col3 from t1 where (col3,col2) in (select max(col3),col2 from t1 group by col2)

        [/code]

        Because one col2 may have a date value equivalent to the max date value of another col2, but that date may not be its max date
        eg:
        col2 col3
        1234 12-may-2006
        2345 12-may-2006
        1234 12-JAN-2007

        The output should be
        1234 12-jan-2007
        2345 12-may-2006.
        But according to the previous query wouldn't it be
        1234 12-may-2006
        2345 12-may-2006
        1234 12-JAN-2007
        ?????

        Was that meaningful?
        Cheers

        Comment

        • febyfelix
          New Member
          • May 2007
          • 9

          #5
          Originally posted by frozenmist
          Hi gangadhar,
          If the requirement is as understood by you the shouldn't your query be like
          [code=sql]
          select col1,col2,col3 from t1 where (col3,col2) in (select max(col3),col2 from t1 group by col2)

          [/code]

          Because one col2 may have a date value equivalent to the max date value of another col2, but that date may not be its max date
          eg:
          col2 col3
          1234 12-may-2006
          2345 12-may-2006
          1234 12-JAN-2007

          The output should be
          1234 12-jan-2007
          2345 12-may-2006.
          But according to the previous query wouldn't it be
          1234 12-may-2006
          2345 12-may-2006
          1234 12-JAN-2007
          ?????

          Was that meaningful?
          Cheers


          HOPE THIS WORKS.....


          SELECT * FROM t1 A
          WHERE ROWNUM = (SELECT MAX(ROWNUM) FROM t1 C WHERE C.COL3=A.COL3 GROUP BY C.COL2 )

          Comment

          • chandu031
            Recognized Expert New Member
            • Mar 2007
            • 77

            #6
            Originally posted by frozenmist
            Hi gangadhar,
            If the requirement is as understood by you the shouldn't your query be like
            [code=sql]
            select col1,col2,col3 from t1 where (col3,col2) in (select max(col3),col2 from t1 group by col2)

            [/code]

            Because one col2 may have a date value equivalent to the max date value of another col2, but that date may not be its max date
            eg:
            col2 col3
            1234 12-may-2006
            2345 12-may-2006
            1234 12-JAN-2007

            The output should be
            1234 12-jan-2007
            2345 12-may-2006.
            But according to the previous query wouldn't it be
            1234 12-may-2006
            2345 12-may-2006
            1234 12-JAN-2007
            ?????

            Was that meaningful?
            Cheers
            Hi Frozenmist,

            A slight correction to your query:

            [code=sql]


            SELECT col1,col2,col3 FROM t1 WHERE (col1,col3,col2 ) IN (SELECT min(col1),max(c ol3),col2 FROM t1 GROUP BY col2)

            [/code]

            You had missed out the Min....

            Cheers!

            Comment

            • frozenmist
              Recognized Expert New Member
              • May 2007
              • 179

              #7
              Hi Chandu,
              Thanks for correcting me...
              Ya it wouldn't give proper answer , if there are duplicate values for col2 and col3...

              Cheers

              Comment

              Working...