How to Summary a table in Oracle with Dates

Question

I have this table called HISTORY:

CREATE TABLE HISTORY (START_DATE DATE, POSITION  VARCHAR2(80 BYTE));

The table is populated like this:

START_DATE    POSITION
15-jun-1983   SUPPLY ATTENDANT
01-abr-1987   SUPPLY HELPER
01-mar-1990   SUPPLY ASSISTANT
01-mai-1991   TECHNICAL SUPLY ASSISTENT
01-abr-1999   TECHNICAL SUPLY ASSISTENT SR
01-dez-1999   SUPPLY TECHNICIAN
01-mai-2001   SUPERVISOR
01-jul-2002   SUPERVISOR
01-jan-2004   SECTOR MANAGER
01-jul-2005   SECTOR MANAGER
01-fev-2006   SECTOR MANAGER
01-jan-2007   SENIOR SUPPLY TECHNICIAN
01-mai-2007   SECTOR MANAGER
01-fev-2008   SENIOR SUPPLY TECHNICIAN

I want to make a query that summarize it like this way:

START_DATE    POSITION
15-jun-1983   SUPPLY ATTENDANT
01-abr-1987   SUPPLY HELPER
01-mar-1990   SUPPLY ASSISTANT
01-mai-1991   TECHNICAL SUPLY ASSISTENT
01-abr-1999   TECHNICAL SUPLY ASSISTENT SR
01-dez-1999   SUPPLY TECHNICIAN
01-mai-2001   SUPERVISOR
01-jan-2004   SECTOR MANAGER
01-jan-2007   SENIOR SUPPLY TECHNICIAN
01-mai-2007   SECTOR MANAGER
01-fev-2008   SENIOR SUPPLY TECHNICIAN

In a nutshell, I would to like to remove the grouped position repetitions, keeping only the first one. Does anybody know how?

Thanks in advance

And how do you preserve the order????? – Maheswaran Ravisankar Nov 05 '14 at 19:13 — Maheswaran Ravisankar, Nov 05 '14 at 19:13

Maheswaran Ravisankar · Answer 1 · 2014-11-05T20:01:24.790

1

We can do it using the LEAD function.

Comparing the current position with the upcoming position (next record in order stored in database)

WITH my_data AS
  (
    SELECT rownum AS rn,history.* FROM history
  ),
  my_data_ranked AS
  (SELECT START_DATE,
    POSITION,
    rn,
    lead(position,1,0) OVER(order by rn) as lead,
    lag(position,1,0) OVER(order by rn) as lag
  FROM my_data
  )
SELECT * FROM my_data_ranked
where POSITION <> LEAD
ORDER BY rn

edited Nov 05 '14 at 20:01

answered Nov 05 '14 at 19:08

Maheswaran Ravisankar

17,652
6
47
69

Thank you so much for your help Maheswaran!! It opened my mind! It turns out the function LAG was more suitable for me in this case. – James Freitas Nov 06 '14 at 10:20
@jamesfrj I was also trying to keep the order of the data stored. Now it seems you look for order by date. Anyways glad it helped you! – Maheswaran Ravisankar Nov 06 '14 at 10:33

score 1 · Accepted Answer · answered Nov 06 '14 at 10:28

With the help of Maheswaran Ravisankar here is the query that achieves the result

WITH NEW_DATASET
     AS (SELECT START_DATE,
                POSITION,
                NVL (LAG (POSITION) OVER (ORDER BY START_DATE), 'NONE')
                    AS PRIOR_POSITION
           FROM HISTORY)
SELECT START_DATE, POSITION
  FROM NEW_DATASET
 WHERE POSITION <> PRIOR_POSITION;

My interest was to keep the position that had the lowest date without interfering with the other occurences of the same position in the rest of the dataset. For that, LAG did the trick.

Thank you!

How to Summary a table in Oracle with Dates

2 Answers2