Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2004
    Location
    Germany
    Posts
    5

    Question Unanswered: Removing duplicate lines from resultset

    Good Morning!

    I just don't know how to accomplish the following task and would be glad if you could help me in completing this.

    Imagine I have the following resultset:

    PHP Code:
    DT                  Product
    2007
    -04-04 00:10:30 116 <--                   
    2007-04-04 00:10:38 116                    
    2007
    -04-04 00:11:03 116                    
    2007
    -04-04 00:13:28 99 <--                    
    2007-04-04 08:24:19 99                     
    2007
    -04-04 08:24:46 99                     
    2007
    -04-04 08:24:47 99                     
    2007
    -04-04 08:24:51 99                     
    2007
    -04-04 08:24:57 99                     
    2007
    -04-04 08:29:08 116 <--                   
    2007-04-04 08:29:19 116                    
    2007
    -04-04 08:33:49 116                    
    2007
    -04-04 08:46:39 116                    
    2007
    -04-04 08:46:47 116 
    ... and I wish to retrieve only the lines with the arrows - how could I do this? If I use "group by product", I'll get only one row per product, but I need all records where the product has changed along with the point in time when the change happened.

    Thanks for any suggestions!!

    Sascha

  2. #2
    Join Date
    Dec 2007
    Posts
    253
    What version are you on?

  3. #3
    Join Date
    Dec 2004
    Location
    Germany
    Posts
    5

    oracle version

    We are using Oracle 10g.

  4. #4
    Join Date
    Dec 2007
    Posts
    253
    Good. Use the LAG function to compare the current row's product value with the previous row's product value. If the two are not equal then you want to display the row, otherwise you do not. In order to use this restriction with the LAG function, you will need to use a subquery
    Pseudo code:
    Code:
    SELECT prod, date
    FROM (SELECT prod, date, LAG_function to 'see' the previous row
             FROM yourtable)
    WHERE prod<>lag_calc;
    If you need to, you can read up on the LAG function here

  5. #5
    Join Date
    Dec 2004
    Location
    Germany
    Posts
    5
    Thanks mate!!

    That did it but now I gotta accelerate my statement a bit because waiting about 1 minute or so for a query result is probably not acceptable ...

    Anyway, thanks again!!

  6. #6
    Join Date
    Dec 2007
    Posts
    253

    Red face

    Glad to hear that you got it working. Just for completeness, any chance you could post the actual code that you used (obfuscate column names if you need to) just so that anyone else searching can see an example.
    Cheers

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •