CODEWITHSUNDEEP
sqlsql-server
AMorton1989
AMorton1989

Reputation: 313

SQL - 3 column filter

I have a table with 3 columns, 

ID  ----   Site  ----   Date  
1A  -----  A ----10/12/16  
1A  -----  B ----11/12/16  
2A  -----  A ----10/12/16  
3A  -----  A ----09/12/16  
3A  -----  B ----09/12/16  
4A  -----  A ----11/12/16  
5A  -----  A ----11/12/16  
5A  -----  B ----11/12/16  
6A  -----  A ----09/12/16  
6A  -----  B ----10/12/16

I need all rows which have the Primary Site - A
I also need rows with the same ID and Same Date, the Site can be different.
If rows have the same ID but a different date, then I need them filtered out. So the table should look like --

ID  ----   Site  ----   Date  
1A  -----  A ----10/12/16   
2A  -----  A ----10/12/16  
3A  -----  A ----09/12/16  
3A  -----  B ----09/12/16  
4A  -----  A ----11/12/16  
5A  -----  A ----11/12/16  
5A  -----  B ----11/12/16   
6A  -----  A ----09/12/16

Upvotes: 3

Views: 227

Answers (4)

Esperento57
Esperento57

Reputation: 17472

if your sql server is 2012 version (or superior), you can use lag

select * from 
   (
   select *, lag([Date]) over(partition by ID order by [Date]) as valbefor
   from yourtable
   ) tmp where isnull(valbefor, [Date])=[Date]

Upvotes: 0

Esperento57
Esperento57

Reputation: 17472

select * from 
     (
     SELECT *, rank() OVER (PARTITION BY ID order by id, Date) AS rnk
     FROM yourtable 
     ) tmp where rnk=1

Upvotes: 0

Nolan Shang
Nolan Shang

Reputation: 2328

with tb(ID,Site,[Date]) AS(
  select '1A','A','10/12/16' union all
  select '1A','B','11/12/16' union all
  select '2A','A','10/12/16' union all
  select '3A','A','09/12/16' union all
  select '3A','B','09/12/16' union all
  select '4A','A','11/12/16' union all
  select '5A','A','11/12/16' union all
  select '5A','B','11/12/16' union all
  select '6A','A','09/12/16' union all
  select '6A','B','10/12/16')
  select * from (
    SELECT *, count(0)OVER (PARTITION BY ID, Date) AS SameDateCount
    FROM tb
  ) as t where t.site='A' OR t.SameDateCount>1

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521457

SELECT t1.*
FROM yourTable t1
LEFT JOIN
(
    SELECT ID, Date
    FROM yourTable
    GROUP BY ID, Date
    HAVING COUNT(*) > 1
) t2
    ON t1.ID   = t2.ID AND
       t1.Date = t2.Date
WHERE t1.Site = 'A' OR
      t2.ID IS NOT NULL

Explanation:

The subquery identifies ID, Date pairs for which more than one record appear having these values (ignoring the site). The join marks these records, which you want to retain. In the WHERE clause, either duplicate records or records having the 'A' site are retained in the result set.

Upvotes: 6

Related Questions