7 Replies Latest reply on May 29, 2017 4:24 AM by Paulie

    Grouby by and max.

    tbhluehorn

      Good Afternoon,

       

      Please help me construct a select statement to select the lastest record for each hour. I would like the output to only have lastest date3  per hour . Red Records, Pls help me,

       

       

       

      Thank you,

      tbhluehorn

       

       

      Date1                          Date2                     Date3                             Date4

      5/26/2017 12:30    5/26/2017 17:02    5/26/2017 17:02    2017-05-26 17

      5/26/2017 12:45    5/26/2017 17:02    5/26/2017 17:02    2017-05-26 17

      5/26/2017 12:15    5/26/2017 17:02    5/26/2017 17:02    2017-05-26 17

      5/26/2017 12:00    5/26/2017 16:16    5/26/2017 16:16    2017-05-26 16

      5/26/2017 11:45    5/26/2017 16:02    5/26/2017 16:02    2017-05-26 16

      5/26/2017 11:30    5/26/2017 16:02    5/26/2017 16:02    2017-05-26 16

      5/26/2017 11:15    5/26/2017 16:02    5/26/2017 16:02    2017-05-26 16

      5/26/2017 11:00    5/26/2017 15:16    5/26/2017 15:16    2017-05-26 15

      5/26/2017 10:45    5/26/2017 15:03    5/26/2017 15:03    2017-05-26 15

      5/26/2017 10:30    5/26/2017 15:03    5/26/2017 15:03    2017-05-26 15

      5/26/2017 10:15    5/26/2017 15:03    5/26/2017 15:03    2017-05-26 15

      5/26/2017 9:30    5/26/2017 14:16    5/26/2017 14:16    2017-05-26 14

      5/26/2017 9:15    5/26/2017 14:16    5/26/2017 14:16    2017-05-26 14

      5/26/2017 9:45    5/26/2017 14:16    5/26/2017 14:16    2017-05-26 14

      5/26/2017 10:00    5/26/2017 14:16    5/26/2017 14:16    2017-05-26 14

      5/26/2017 9:00    5/26/2017 13:16    5/26/2017 13:16    2017-05-26 13

      5/26/2017 8:45    5/26/2017 13:03    5/26/2017 13:03    2017-05-26 13

      5/26/2017 8:15    5/26/2017 13:03    5/26/2017 13:03    2017-05-26 13

      5/26/2017 8:30    5/26/2017 13:03    5/26/2017 13:03    2017-05-26 13

      5/26/2017 8:00    5/26/2017 12:16    5/26/2017 12:16    2017-05-26 12

      5/26/2017 7:15    5/26/2017 12:03    5/26/2017 12:03    2017-05-26 12

      5/26/2017 7:30    5/26/2017 12:03    5/26/2017 12:03    2017-05-26 12

      5/26/2017 7:45    5/26/2017 12:03    5/26/2017 12:03    2017-05-26 12

      5/26/2017 7:00    5/26/2017 11:16    5/26/2017 11:16    2017-05-26 11

      5/26/2017 6:30    5/26/2017 11:03    5/26/2017 11:03    2017-05-26 11

      5/26/2017 6:15    5/26/2017 11:03    5/26/2017 11:03    2017-05-26 11

      5/26/2017 6:45    5/26/2017 11:03    5/26/2017 11:03    2017-05-26 11

      5/26/2017 6:00    5/26/2017 10:16    5/26/2017 10:16    2017-05-26 10

      5/26/2017 5:45    5/26/2017 10:03    5/26/2017 10:03    2017-05-26 10

      5/26/2017 5:15    5/26/2017 10:03    5/26/2017 10:03    2017-05-26 10

      5/26/2017 5:30    5/26/2017 10:03    5/26/2017 10:03    2017-05-26 10

        • 1. Re: Grouby by and max.
          Frank Kulash

          Hi,

           

          Hi,

           

          Whenever you have a problem, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) from all tables involved, so that the people who want to help you can re-create the problem and test their ideas.

          Always say which version of Oracle you're using (for example, 11.2.0.2.0).

          See the forum FAQ: Re: 2. How do I ask a question on the forums?

          tbhluehorn wrote:

           

          Good Afternoon,

           

          Please help me construct a select statement to select the lastest record for each hour. I would like the output to only have lastest date3 per hour . Red Records, Pls help me,

           

           

           

          Thank you,

          tbhluehorn

           

           

          Date1 Date2 Date3 Date4

          5/26/2017 12:30 5/26/2017 17:02 5/26/2017 17:02 2017-05-26 17

          5/26/2017 12:45 5/26/2017 17:02 5/26/2017 17:02 2017-05-26 17

          5/26/2017 12:15 5/26/2017 17:02 5/26/2017 17:02 2017-05-26 17

          5/26/2017 12:00 5/26/2017 16:16 5/26/2017 16:16 2017-05-26 16

          5/26/2017 11:45 5/26/2017 16:02 5/26/2017 16:02 2017-05-26 16

          5/26/2017 11:30 5/26/2017 16:02 5/26/2017 16:02 2017-05-26 16

          5/26/2017 11:15 5/26/2017 16:02 5/26/2017 16:02 2017-05-26 16

          ...

          You can use analytic functions, such as MAX or ROW_NUMBER, to do that.

           

          I don't have a copy of your table, so I'll use scott.emp to illustrate.  Where you want to get the row with the latest date3 for each hour, the query below gets the row with the latest hiredate for each year:

          WITH    got_max_hiredate    AS

          (

              SELECT  ename, deptno, job, hiredate   -- or whatever columns you want

              ,       MAX (hiredate) OVER (PARTITION BY  TRUNC (hiredate, 'YEAR'))

                          AS max_hiredate

              FROM    scott.emp

          )

          SELECT    ename, deptno, job, hiredate

          FROM      got_max_hiredate

          WHERE     hiredate  = max_hiredate

          ORDER BY  hiredate

          ;

          Output:

          ENAME          DEPTNO JOB       HIREDATE

          ---------- ---------- --------- -----------

          SMITH              20 CLERK     17-Dec-1980

          FORD               20 ANALYST   03-Dec-1981

          JAMES              30 CLERK     03-Dec-1981

          MILLER             10 CLERK     23-Jan-1982

          ADAMS              20 CLERK     23-May-1987

          What do you want to do in case of a tie?

          In the scott.emp table, there happens to be a tie in the year 1981.  The last hiredate that year was December 3, and that value occurred on 2 rows.  Both rows are included in the result set.  If you want only 1 row per year, use ROW_NUMBER instead of MAX.

          • 2. Re: Grouby by and max.
            Frank Kulash

            Hi,

             

            By the way, you can get the desired results using GROUP BY and the aggregate LAST function, but it's messy and inefficient.   You'd need to call the aggregate LAST function separately for each column in the output. 

             

            Also, analytic functions give you more options about how to handle ties, and you can easily change the query to show (for example) the 3 latest rows for each hour, not just the 1 latest row.

            • 3. Re: Grouby by and max.
              tbhluehorn

              Please let me know where i can get the scott.emp tables.

              • 4. Re: Grouby by and max.
                BluShadow

                tbhluehorn wrote:

                 

                Please let me know where i can get the scott.emp tables.

                 

                 

                The schema is installed as part of the database, however the account may be locked if the DBA's that installed the database didn't open it up when they installed.  If the account get's unlocked then you will be able to log in and use it.

                • 5. Re: Grouby by and max.
                  Frank Kulash

                  Hi,

                  tbhluehorn wrote:

                   

                  Please let me know where i can get the scott.emp tables.

                  See this thread:

                  Re: How to add SCOTT schema to my database?

                   

                  If you can't create a scott schema, you can create the tables in any other schema.

                  • 6. Re: Grouby by and max.
                    tbhluehorn

                    That I can do, now to find the create and insert statements.

                    • 7. Re: Grouby by and max.
                      Paulie

                      tbhluehorn wrote:

                       

                      That I can do, now to find the create and insert statements.

                       

                      I know you're new but really!

                       

                      Mr. Google is your friend.

                       

                      Try this for example - SCOTT - Oracle FAQ or here https://dba.stackexchange.com/questions/63457/installing-scott-schema-for-oracle-11g-express

                       

                      Both links obtained from Googling "oracle get scott schema script".

                       

                      You can run 11gXE for free on any machine with RAM >= 1GB, see:

                       

                      Oracle Database Express Edition 11g Release 2 Downloads

                       

                      And

                       

                      Oracle Database 11g Express Edition Quick Tour

                       

                      HTH...