This discussion is archived
4 Replies Latest reply: Jan 29, 2013 12:17 PM by user1991209 RSS

Screening rows that are not equal

user1991209 Newbie
Currently Being Moderated
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 Explorer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    While all the solutions are correct, and I thank everyone who responded, i chose this solution.
    Thanks
    Paul

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points