1 2 Previous Next 19 Replies Latest reply: May 26, 2009 3:30 AM by user10378039 RSS

    SQL: select max value of a specific record

    user10378039
      Hi,

      I'm trying to create a view that contains data from 4 different tables.

      1 table is the main table (must contain data) and the other 3 can be empty for the "link-key".

      eg:
      ...
      WHERE
      myTable1.mykey = mytable2.mykey(+)
      myTable1.mykey = mytable3.mykey(+)
      myTable1.mykey = mytable4.mykey(+)
      ...

      Now the issue is that tables 2-4 can have more than 1 entry in them for a specific "myKey". For the view I would like to only select the newest entry for any specific "mykey". newest = filter according to a date field.

      How do I do that?
        • 1. Re: SQL: select max value of a specific record
          SeánMacGC
          Hello,
          Assuming that you don't wast to JOIN to multiple records in your tables 2-4, you could create inline views for these, and JOIN on the max date within each, i.e.,
          WITH max_t2 AS (
          SELECT MAX(myKey)
            FROM 
          TABLE1 Table2 T2),
          max_t3 AS(...
          )
          SELECT *
            FROM Table1
           LEFT JOIN (SELECT col1,myKey
                            FROM...
                                  ) T2
             ON (T1.myKey = max_t2.myKey)
            LEFT JOIN (SElECT...
          • 2. Re: SQL: select max value of a specific record
            user10378039
            I want exactly 1 row for each key.

            To be more specific i want max(date) and not max(mykey)

            What is the WITH statment?
            • 3. Re: SQL: select max value of a specific record
              SeánMacGC
              OK,

              Try:
              WITH max_t2 AS (
              SELECT MAX(date_col) max_date
                FROM 
              TABLE1 Table2 T2),
              max_t3 AS(..
              And join on that.

              The WITH clause allows you to extract the values you need from a subquery first, and is specifically helpful when there's more than one reference in the body of the main SQL itself, however, it can help to improve the readability of the query.
              • 4. Re: SQL: select max value of a specific record
                user10378039
                ok. will have to try a little maybe i will get it then.

                Note that tables 2-4 all have an independent date column, date of table 2 has nothing to do with date of table 3.

                EDIT:

                the thing I don't get is how the max value is linked to 1 specific record.

                I first filter for "mykey" and then I want to filter for the newest record in the child tables with this "mykey". I'm not saying your wrong but I rather prefer to understand what I'm doing.

                Edited by: user10378039 on 25.05.2009 03:35
                • 5. Re: SQL: select max value of a specific record
                  SeánMacGC
                  user10378039 wrote:
                  Note that tables 2-4 all have an independent date column, date of table 2 has nothing to do with date of table 3.
                  Yes, that's OK, it's the association with Table1 that's the important one.
                  • 6. Re: SQL: select max value of a specific record
                    630199
                    user10378039 wrote:
                    ok. will have to try a little maybe i will get it then.

                    Note that tables 2-4 all have an independent date column, date of table 2 has nothing to do with date of table 3.

                    EDIT:

                    the thing I don't get is how the max value is linked to 1 specific record.

                    I first filter for "mykey" and then I want to filter for the newest record in the child tables with this "mykey". I'm not saying your wrong but I rather prefer to understand what I'm doing.

                    Edited by: user10378039 on 25.05.2009 03:35
                    In that case you can do something like:
                    WITH max_t2 AS (
                    SELECT mykey, MAX(date_col) max_date
                      FROM 
                    TABLE2 T2 
                    GROUP BY mykey),
                    max_t3 AS
                    (SELECT mykey, MAX(date_col) max_date
                      FROM 
                    TABLE 3 T3 
                    GROUP BY mykey),
                    max_t4 AS
                    (...
                    In this case for each table you have the latest records for each mykey.

                    Hope this helps.

                    Regards,
                    Jo
                    • 7. Re: SQL: select max value of a specific record
                      user10378039
                      see below sqlö without the date filter:

                      CREATE OR REPLACE VIEW MY_DATA
                      (
                      myKey,
                      parentValues,
                      cost,
                      gcth,
                      vp,
                      oth,
                           ov
                      )
                      AS

                      SELECT

                           mi.myKey,
                           mi.parentvalues,     
                           cev.cost,
                           gc.gcth,     
                           fi.vp,
                           fi.oth,
                           fi.ov

                      FROM
                           AV_1356_1395 mi,
                           AV_1355_1394 cev,
                           AV_1227_1220 gc,
                           AV_1187_1180 fi
                      WHERE
                           mi.myKey = cev.myKey(+)
                           AND
                           (mi.myKey = gc.myKey(+)
                           AND
                           (mi.myKeyr = fi.myKey(+)


                      as mentioned in my edited I don't get how max(date_column) is linked to a specific key value.

                      EDIT:

                      ok thanks. did not see last post before this one.

                      Edited by: user10378039 on 25.05.2009 03:59
                      • 8. Re: SQL: select max value of a specific record
                        SeánMacGC
                        user10378039 wrote:
                        I first filter for "mykey" and then I want to filter for the newest record in the child tables with this "mykey". I'm not saying your wrong but I rather prefer to understand what I'm doing.
                        OK, in each of the WITH clauses you want only one record returned, and this will be used to JOIN to Table1. There are several ways that this can be achieved:
                        SELECT myKey 
                           FROM table2 
                         WHERE date_col = (SELECT MAX(date_col)
                            FROM table2)
                        Or:
                        SELECT myKey
                           FROM (
                          SELECT myKey,
                                     ROW_NUMBER() OVER (ORDER BYdate_col DESC NULLS LAST) row_num
                            FROM table2)
                         WHERE row_num = 1
                        Both of those will return the myKey associated with the most recent record by DATE.
                        • 9. Re: SQL: select max value of a specific record
                          user10378039
                          and how to i get my data?

                          eg:
                          ..
                          With max_t2 AS(
                          SELECT myKey, mydata
                          FROM table2
                          WHERE date_col = (SELECT MAX(date_col)
                          FROM table2)
                          )
                          ...

                          or do i have to keep my original sql and add the with statement to it?

                          (I'm not an expert, but that is probaly clear by now ;) )

                          Edited by: user10378039 on 25.05.2009 04:26
                          • 10. Re: SQL: select max value of a specific record
                            SeánMacGC
                            No worries. If you go back to my first post you'll see how it's referenced in the JOIN, i.e., whatever name is given in the WITH clause, that can be treated as a table name thereafter.

                            For example:
                            With max_t2 AS(
                            SELECT myKey, mydata
                            FROM table2
                            WHERE date_col = (SELECT MAX(date_col)
                            FROM table2)
                            )
                            ...
                            SELECT * 
                              FROM max_t2
                            • 11. Re: SQL: select max value of a specific record
                              630199
                              You can change your select statement

                              FROM
                              SELECT 
                              
                              mi.myKey, 
                              mi.parentvalues, 
                              cev.cost,
                              gc.gcth, 
                              fi.vp, 
                              fi.oth, 
                              fi.ov 
                              FROM
                              AV_1356_1395 mi,
                              AV_1355_1394 cev,
                              AV_1227_1220 gc, 
                              AV_1187_1180 fi
                              WHERE 
                              mi.myKey = cev.myKey(+) 
                              AND
                              (mi.myKey = gc.myKey(+)
                              AND
                              (mi.myKeyr = fi.myKey(+) 
                              TO
                              SELECT mi.mykey, mi.parentvalues, cev.COST, gc.gcth, fi.vp, fi.oth, fi.ov
                                FROM av_1356_1395 mi,
                                     (SELECT *
                                        FROM av_1355_1394 inq_1
                                       WHERE (inq_1.mykey, inq_1.date_column) =
                                                                          (SELECT   mykey,
                                                                                    MAX (date_column)
                                                                               FROM av_1355_1394 inq_2
                                                                              WHERE inq_2.mykey =
                                                                                                 inq_1.mykey
                                                                           GROUP BY inq_2.mykey)) cev,
                                     (SELECT *
                                        FROM av_1227_1220 inq_1
                                       WHERE (inq_1.mykey, inq_1.date_column) =
                                                                          (SELECT   mykey,
                                                                                    MAX (date_column)
                                                                               FROM av_1227_1220 inq_2
                                                                              WHERE inq_2.mykey =
                                                                                                 inq_1.mykey
                                                                           GROUP BY inq_2.mykey)) gc,
                                     (SELECT *
                                        FROM av_1187_1180 inq_1
                                       WHERE (inq_1.mykey, inq_1.date_column) =
                                                                          (SELECT   mykey,
                                                                                    MAX (date_column)
                                                                               FROM av_1187_1180 inq_2
                                                                              WHERE inq_2.mykey =
                                                                                                 inq_1.mykey
                                                                           GROUP BY inq_2.mykey)) fi
                               WHERE mi.mykey = cev.mykey(+) 
                               AND mi.mykey = gc.mykey(+) 
                               AND mi.mykeyr = fi.mykey(+)
                              Hope this helps

                              Regards,
                              Jo
                              • 12. Re: SQL: select max value of a specific record
                                user10378039
                                ok will try and what about the same in "WITH-Syntax"?

                                Which one is better (= faster)?

                                EDIT:

                                This does not work:

                                WITH
                                     cev AS
                                          (
                                          SELECT
                                               mykey,
                                               mydata     
                                          FROM
                                               AV_1355_1394
                                          WHERE
                                               Report_Date = (SELECT MAX(REPORT_DATE)
                                          FROM
                                               AV_1355_1394)
                                          ),

                                     gc AS
                                          (          
                                          SELECT
                                               mykey,
                                               mydata               
                                          FROM
                                               AV_1227_1220
                                          WHERE
                                               MEASURING_DATE = (SELECT MAX(MEASURING_DATE)
                                          FROM
                                               AV_1227_1220)
                                          ),
                                          
                                     fi AS
                                          (
                                          SELECT
                                               mykey,
                                               mydata
                                          FROM
                                               AV_1187_1180
                                          WHERE
                                               MEASURING_DATE = (SELECT MAX(MEASURING_DATE)
                                          FROM
                                               AV_1187_1180)
                                          ),
                                SELECT
                                     mi.mykey,
                                     mi.mydata,
                                     cev.mydata,
                                     gc.mydata,     
                                     fi.mydata

                                FROM
                                     AV_1356_1395 mi
                                     LEFT JOIN cev
                                          ON mi.mykey = cev.mykey
                                     LEFT JOIN gc
                                          ON mi.mykey = gc.mykey
                                     LEFT JOIN gc
                                          ON mi.mykey = fi.mykey

                                "fi.mykey" invalid identifier

                                Edited by: user10378039 on 25.05.2009 05:21

                                Edited by: user10378039 on 25.05.2009 06:24
                                • 13. Re: SQL: select max value of a specific record
                                  SeánMacGC
                                  user10378039 wrote:
                                       LEFT JOIN gc
                                            ON mi.mykey = gc.mykey
                                       LEFT JOIN gc
                                            ON mi.mykey = fi.mykey

                                  "fi.mykey" invalid identifier
                                  You are joining on gc twice, it should be:
                                   LEFT JOIN gc
                                    ON mi.mykey = gc.mykey
                                   LEFT JOIN fi
                                    ON mi.mykey = fi.mykey
                                  • 14. Re: SQL: select max value of a specific record
                                    630199
                                    user10378039 wrote:
                                    CREATE OR REPLACE VIEW MAP_DATA
                                    (
                                         mykey,
                                         REG_NUMBER,
                                         BATCH_NUMBER,
                                         CHEMIST,
                                         EVALUATION_DATE,
                                         COMPLETE_ODOR_PROFILE,
                                         TENACITY,
                                         INTENSITY,      
                                         ASSESSOR,
                                         COST_ESTIMATE_RESEARCH,
                                         COST_ESTIMATE_VERNIER,
                                         GC_THRESHOLD,
                                         VAPOR_PRESSURE,
                                         OTH_GEOM,
                                         ODOR_VALUE
                                    )
                                    AS
                                    ..... (With Clause Block)
                                    SELECT
                                         mi.mykey,
                                         mi.mydata,
                                         cev.mydata,
                                         gc.mydata,     
                                         fi.mydata

                                    FROM
                                         AV_1356_1395 mi
                                         LEFT JOIN cev
                                              ON mi.mykey = cev.mykey
                                         LEFT JOIN gc
                                              ON mi.mykey = gc.mykey
                                         LEFT JOIN gc
                                              ON mi.mykey = fi.mykey
                                    Once you have fixed the query as pointed out by Sean, i think you will run into the following error:
                                    SQL> CREATE OR REPLACE VIEW test_view (col_1, col_2)
                                      2  AS
                                      3     SELECT 'A' col_1
                                      4       FROM DUAL;
                                    CREATE OR REPLACE VIEW test_view (col_1, col_2)
                                                                      *
                                    ERROR at line 1:
                                    ORA-01730: invalid number of column names specified
                                    
                                    
                                    Elapsed: 00:00:00.32
                                    SQL> 
                                    The number of columns in your CREATE Statement should be equal to the number of rows in your SELECT Statement.

                                    Regards,
                                    Jo
                                    1 2 Previous Next