8 Replies Latest reply: Apr 7, 2006 2:40 PM by 445741 RSS

    SQL in distinct row with latest date

    504828
      Hi, Currently i have 2 rows

      ID DATE
      ------------------------------------
      2222 13/01/2006
      1111 13/01/2006
      1111 14/01/2006
      3333 14/01/2006


      I want to display the row with the latest date ONLY

      ID DATE
      ------------------------------------
      2222 13/01/2006
      1111 14/01/2006
      3333 14/01/2006

      How am i going to write the query?

      SELECT ID, DATE FROM TABLE
      ?????

      Thanks

      Message was edited by:
      user501825
        • 1. Re: SQL in distinct row with latest date
          orawarebyte
          hi

          SELECT id,MAX(date)
          FROM <<table>>
          GROUP BY id;

          Moreever Date is keyword.

          Khurram Siddiqui
          oraware@yahoo.com
          • 2. Re: SQL in distinct row with latest date
            504828
            thanks, but let's say the entire table is like this

            ID1 ID DATE ENCRYPT
            --------------------------------------
            1 2222 13/01/2006 aaaaa
            2 1111 13/01/2006 bbbbb
            3 1111 14/01/2006 ccccc
            4 3333 14/01/2006 ddddd

            and the expected result is as below

            ID1 ID DATE
            --------------------------------------
            1 2222 13/01/2006 aaaaa
            3 1111 14/01/2006 ccccc
            4 3333 14/01/2006 ddddd

            when using the solution given above, i get the error message "not a GROUP BY expression"

            Message was edited by:
            darontan
            • 3. Re: SQL in distinct row with latest date
              495612
              SELECT MAX(ID1), ID , MAX(DT) , MAX(ENCRPT) FROM TABLE1
              GROUP BY ID ;
              • 4. Re: SQL in distinct row with latest date
                504828
                thanks, but this is just the examples, cause the encrypt value is much more complicated as shown
                the challenge i'm facing is, i need ALL the fields, but there might be duplicated ID and DATE
                therefore, i only need 1 ID which consist the latest date

                ID1 ID DATE ENCRYPT
                --------------------------------------
                1 2222 13/01/2006 asphfsda
                2 1111 13/01/2006 #$EWYPRU
                3 1111 14/01/2006 WEPROHEW
                4 3333 14/01/2006 YWER(C

                and the expected result is as below


                ID1 ID DATE ENCRYPT
                --------------------------------------
                1 2222 13/01/2006 asphfsda
                3 1111 14/01/2006 WEPROHEW
                4 3333 14/01/2006 YWER(C
                • 5. Re: SQL in distinct row with latest date
                  orawarebyte
                  hi
                  SQL> SELECT * FROM t3
                    2  WHERE rowid IN
                    3  (SELECT max(rowid) FROM t3 GROUP BY id);

                         ID VDATE           ID1 ENC
                  --------- --------- --------- --------------------
                       2222 13-JAN-06         1 asphfsda
                       1111 14-JAN-06         3 WEPROHEW
                       3333 14-JAN-06         4 YWER(C
                  Khurram Siddiqui
                  oraware@yahoo.com
                  • 6. Re: SQL in distinct row with latest date
                    John Spencer
                    SELECT id, id1, date, encrypt
                    FROM (SELECT id, id1, date, encrypt,
                                 ROW_NUMBER() OVER (PARTITION BY id
                                                    ORDER BY date DESC) rn
                          FROM table)
                    WHERE rn = 1
                    Is one way.
                    SELECT id, id1, date, encrypt
                    FROM table o
                    WHERE date = (SELECT MAX(date)
                                  FROM table i
                                  WHERE o.id = i.id)
                    Is another.

                    Khurram:

                    ROWID is the physical address of the row on disk and has no connection at all to either the order of insertion in the table, or the value of any particular column.

                    TTFN
                    John
                    • 7. Re: SQL in distinct row with latest date
                      Gabe2
                      <quote>I want to display the row with the latest date ONLY</quote>

                      ID1 ID DATE ENCRYPT
                      --------------------------------------
                      1 2222 13/01/2006 asphfsda
                      2 1111 13/01/2006 #$EWYPRU

                      ID1 ID DATE ENCRYPT
                      --------------------------------------
                      1 2222 13/01/2006 asphfsda

                      Can you explain why the row with ID=2222 has a DATE qualifying as “latest” vis-à-vis of ID=1111? They look the same to me.
                      • 8. Re: SQL in distinct row with latest date
                        445741
                        Here's a link to a great discussion of this topic:
                        http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:2165134263446