Have Geek Brain

SQL - Suppress Duplicate * Adjacent * Records

I need to run a Select statement (DB2 SQL) that does not pull out adjacent duplicate rows based on a specific field. Specifically, I'm trying to figure out when the data changes, which is difficult as it can revert to its original value.

That is, I have a table that vaguely resembles the below, sorted by letter and then by date:

A, 5, 2009-01-01
A, 12, 2009-02-01
A, 12, 2009-03-01
A, 12, 2009-04-01
A, 9, 2009-05-01
A, 9, 2009-06-01
A, 5, 2009-07-01

And I want to get the results:

A, 5, 2009-01-01
A, 12, 2009-02-01
A, 9, 2009-05-01
A, 5, 2009-07-01

discarding adjacent duplicates, but keeping the last line (even though it has the same number as the first line). Obvious:

Select Letter, Number, Min(Update_Date) from Table group by Letter, Number

doesn't work - it doesn't include the last line.

Edit: Since there seems to be some confusion, I clarified the month column in the date column. This meant that it was a Russian formal form, and not as factual reliable data.

Edit: The last line is not important because it is the last line, but because it has a "new value" which is also an "old value". Grouping by NUMBER will end up in the first row; it must remain a separate entity.

+2


a source to share


5 answers


Depending on which DB2 you are working with, there are analytical functions that can alleviate this problem. An example in Oracle is shown below, but the select syntax looks pretty similar.



create table t1 (c1 char, c2 number, c3 date);

insert into t1 VALUES ('A', 5, DATE '2009-01-01');
insert into t1 VALUES ('A', 12, DATE '2009-02-01');
insert into t1 VALUES ('A', 12, DATE '2009-03-01');
insert into t1 VALUES ('A', 12, DATE '2009-04-01');
insert into t1 VALUES ('A', 9, DATE '2009-05-01');
insert into t1 VALUES ('A', 9, DATE '2009-06-01');
insert into t1 VALUES ('A', 5, DATE '2009-07-01');

SQL> l
  1  SELECT C1, C2, C3
  2    FROM (SELECT C1, C2, C3,
  3                 LAG(C2) OVER (PARTITION BY C1 ORDER BY C3) AS PRIOR_C2,
  4                 LEAD(C2) OVER (PARTITION BY C1 ORDER BY C3) AS NEXT_C2
  5            FROM T1
  6         )
  7   WHERE C2 <> PRIOR_C2
  8      OR PRIOR_C2 IS NULL -- to pick up the first value
  9   ORDER BY C1, C3
SQL> /

C         C2 C3
- ---------- -------------------
A          5 2009-01-01 00:00:00
A         12 2009-02-01 00:00:00
A          9 2009-05-01 00:00:00
A          5 2009-07-01 00:00:00
+3


a source


This is impossible when typing commands (i.e., a group of and so).

You can do this using cursors.



Personally, I get the data in my client app and do the filtering there.

+1


a source


The first thing you need to do is determine the sequence in which you want to view / view the data. The values ​​"Jan, Feb, Mar" are not helpful since the data is not in alphabetical order. And what happens when you roll over from December to Yang? Step 1. Determine a sequence that uniquely identifies each line in relation to your problem.

Then you should be able to compare #x to # x-1 to see if it has changed. If changed, enable; if not changed, delete. It's trivial when using procedural code loops (cursors in SQL), but would you like to use them? They usually don't work too well.

One SQL based way is to join this table by itself and the join clause is "MyTable.SequenceVal = MyTable.SequenceVal - 1". Throw in the comparison, make sure you don't throw the first row of the set (where there is no x-1) and you're done. Note that performance can suck if "SequenceVal" is not indexed.

+1


a source


discarding adjacent duplicates, but keeping the last line.

why do you want to keep the last line? What's the purpose?

0


a source


Using the "EXCEPT" clause is one way to do this. See below solution. All my test steps were included here. First, I created a session table (this will disappear after disconnecting from my database).

CREATE TABLE session.sample (
   letter CHAR(1),
   number INT,
   update_date DATE
);

Then I imported your sample data:

IMPORT FROM sample.csv OF DEL INSERT INTO session.sample;

Checked that your sample information is in the database:

SELECT * FROM session.sample;

 LETTER NUMBER      UPDATE_DATE
 ------ ----------- -----------
 A                5 01/01/2009
 A               12 02/01/2009
 A               12 03/01/2009
 A               12 04/01/2009
 A                9 05/01/2009
 A                9 06/01/2009
 A                5 07/01/2009

   7 record(s) selected.

I wrote this with an EXCEPT clause and used "WITH" to try and make it clearer. Basically, I am trying to select all rows with the previous date entry. Then I exclude all those rows from the list in the whole table.

WITH rows_with_previous AS (
  SELECT s.*
  FROM session.sample s
  JOIN session.sample s2
    ON s.letter = s2.letter
      AND s.number = s2.number
      AND s.update_date = s2.update_date - 1 MONTH
)
SELECT *
FROM session.sample
EXCEPT ALL
SELECT *
FROM rows_with_previous;

Here's the result:

 LETTER NUMBER      UPDATE_DATE
 ------ ----------- -----------
 A                5 01/01/2009
 A               12 04/01/2009
 A                9 06/01/2009
 A                5 07/01/2009

   4 record(s) selected.
0


a source






More articles:


All Articles