CODEWITHSUNDEEP
sql
user3736877
user3736877

Reputation: 11

SQL How to filter changes in columns

My problem is the following: 

ID   Value   Date
---------------------------------
ID1    1     2014.03.03
ID2    1     2014.03.04    
ID1    2     2014.03.04  
ID3    1     2014.03.04  
ID2    1     2014.03.05  
ID2    1     2014.03.05   
ID1    2     2014.03.05    
ID1    2     2014.03.05
ID1    2     2014.03.05
ID1    1     2014.03.05
ID1    3     2014.03.05

And I would like the following output:

ID   Value   Date
---------------------------------
ID1    1     2014.03.03    
ID1    2     2014.03.04  
ID3    1     2014.03.04  
ID2    1     2014.03.05 
ID1    1     2014.03.05  
ID1    3     2014.03.05

So if ID1's value is 1 I'd like to get only once (the date doesn't matter it could be the newest or the oldest). But if the value changes (even to an earlier value) I'd like to write it to the output. How can I do that?

Upvotes: 0

Views: 68

Answers (2)

user330315
user330315

Reputation:

You didn't state your DBMS so this is ANSI SQL.

Something like this might do:

select id, value, date
from (
   select id, value, date, 
          lag(value) over (order by id, date) as prev_value
   from the_table
) t
where value <> prev_value
order by id, value;

It selects all rows where the value in the column value (btw: a horrible name for a column) is different than the value of that column in the previous row.

Upvotes: 2

Blood-HaZaRd
Blood-HaZaRd

Reputation: 2138

Try to use DISTINCT.

Select distinct ID, Value, Date From YOUR_TABLE

So for Tuples if there are data having Same values for all the 3 columns it will show only one instance of the data. But if one of the columns has a different data it will consider it as different from previous and kepp it in the output.

Upvotes: -1

Related Questions