4 Replies Latest reply: Jan 29, 2013 2:17 PM by user1991209 RSS

    Screening rows that are not equal

    user1991209
      The PL/SQL version I am using is 11.1.0.7.0

      This is sample data

      Id Date Seq Station
      __ ____ __ ______
      1 3/21/2012 12 1206
      1 2/28/2012 24 1712
      1 2/12/2012 20 1105

      2 3/20/2012 45 1206
      2 2/26/2012 46 1206
      2 2/22/2012 47 1206

      3 3/25/2012 10 1206
      3 2/18/2012 12 1105

      4 4/21/2012 22 1105
      4 4/20/2012 23 1105
      4 2/16/2012 24 1105
      4 2/16/2012 26 1105

      What is required is the highest date for each Id, but only if the station code is the same for all records, otherwise ignore all the records.

      The result expected are
      Id Date Seq Station
      __ ____ __ ______

      2 3/20/2012 45 1206
      4 4/21/2012 22 1105

      Thanks
      Paul
        • 1. Re: Screening rows that are not equal
          Alex Fatkulin
          select id, max(dt), max(seq) keep (dense_rank last order by dt), min(station)
               from <table>
               group by id
               having min(station)=max(station);
          • 2. Re: Screening rows that are not equal
            Solomon Yakobson
            SQL> select  *
              2    from  tbl
              3  /
            
               ID DT               SEQ    STATION
            ----- --------- ---------- ----------
                1 21-MAR-12         12       1206
                1 28-FEB-12         24       1712
                1 12-FEB-12         20       1105
                2 20-MAR-12         45       1206
                2 26-FEB-12         46       1206
                2 22-FEB-12         47       1206
                3 25-MAR-12         10       1206
                3 18-FEB-12         12       1105
                4 21-APR-12         22       1105
                4 20-APR-12         23       1105
                4 16-FEB-12         24       1105
            
               ID DT               SEQ    STATION
            ----- --------- ---------- ----------
                4 16-FEB-12         26       1105
            
            12 rows selected.
            
            SQL> with t as (
              2             select  tbl.*,
              3                     count(distinct(station)) over(partition by id) cnt
              4               from  tbl
              5            )
              6  select  id,
              7          max(dt)
              8    from  t
              9    where cnt = 1
             10    group by id
             11    order by id
             12  /
            
               ID MAX(DT)
            ----- ---------
                2 20-MAR-12
                4 21-APR-12
            
            SQL> 
            SY.

            Edited by: Solomon Yakobson on Jan 29, 2013 1:56 PM
            • 3. Re: Screening rows that are not equal
              Frank Kulash
              Hi,

              This sounds like a job for analytic functions:
              WITH   got_analytics     AS
              (
                   SELECT     id
                   ,     dt     -- DATE is not a good column name
                   ,     seq
                   ,     station
                   ,     COUNT (DISTICT station) OVER ( PARTITION BY  id)     AS station_cnt
                   ,     RANK ()                      OVER ( PARTITION BY  id
                                                   ORDER BY          dt   DESC
                                            )                    AS r_num
                   FROM    table_x
              )
              SELECT     id, dt, seq, station
              FROM     got_analytics
              WHERE     station_cnt     = 1
              AND     r_num          = 1
              ;
              If station is NULL, what do you want to do? Depending on your requirements, you may need to do something a little more complicated, but only a little.
              If there is a tie for the latest row, what do you want to do? You may need to use ROW_NUMBER instead of RANK, and/or add tie-breaking expressions to the analytic ORDER BY clause.


               

              I hope this answers your question.
              If not, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only), and also post the results you want from that data.
              Explain, using specific examples, how you get those results from that data.
              Always say which version of Oracle you're using (e.g., 11.2.0.2.0).
              See the forum FAQ {message:id=9360002}
              • 4. Re: Screening rows that are not equal
                user1991209
                While all the solutions are correct, and I thank everyone who responded, i chose this solution.
                Thanks
                Paul