CODEWITHSUNDEEP
sqlsql-servert-sql
sebnears
sebnears

Reputation: 63

Excluding the data with two consecutive conditions in Sql

I have a table looks like this.

Numbers   No         ZNo      Place   Year     AId                          ABC
2   201905190611122 9208363 A/C/T/0/434 2019    4BBA17BB-01A9-41A6-BFA7-004CA0E6686F    1448
2   201802262493590 9208363 A/C/T/0/434 2018    4A895857-4E51-4ADC-836A-22D04E5D0B62    2008
1   20180119827875  9208364 A/C/T/0/435 2018    89BFD858-92AC-463B-91DF-54C22FDF7517    1150
1   20180119827875  9208365 A/C/T/0/436 2018    89BFD858-92AC-463B-91DF-54C22FDF7517    1150
2   201804273541023 9208366 A/C/T/0/437 2018    B01EFCA6-8397-4FA9-9EAD-13BE985D63DD    1348
2   201905197566364 9208366 A/C/T/0/437 2019    43E3D908-4AAD-4832-9981-115A5F9E9FC3    1466
2   201802084364285 9208367 A/C/T/0/438 2018    20BB4E90-6F59-484E-ADD3-5635F7CAACC3    1138
2   201802091458406 9208367 A/C/T/0/438 2018    E9085238-8437-4628-A125-09E5C811AB8D    1248

I want to write down a query that will first find same "Place" values. Then it will check out the "Year" columns. If the year values are the same for same place values, the data will be kept. So, it will basically should look like this:

Numbers   No         ZNo      Place   Year     AId                          ABC
2   201802084364285 9208367 A/C/T/0/438 2018    20BB4E90-6F59-484E-ADD3-5635F7CAACC3    1138
2   201802091458406 9208367 A/C/T/0/438 2018    E9085238-8437-4628-A125-09E5C811AB8D    1248

Can you help me with this?

Upvotes: 0

Views: 48

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270191

You can use window functions:

select t.*
from (select t.*, count(*) over (partition by place, year) as cnt
      from t
     ) t
where cnt >= 2;

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You can use not exists :

select t.*
from table t
where not exists (select 1 from table t1 where t1.Place = t.Place and t1.year <> t.year);

By this way, you will also get A/C/T/0/435 & A/C/T/0/436 as because it doesn't have a other years too. So, you can add other unique column in sub-query. I found no is unique in sample data :

select t.*
from table t
where not exists (select 1 
                  from table t1 
                  where t1.Place = t.Place and t1.year <> t.year and t1.no <> t.no
                 );

Upvotes: 1

jarlh
jarlh

Reputation: 44786

Have a derived table (the subquery) returning place/year combinations that exist at least twice. JOIN its result:

select t.*
from tablename t
join (select Place, Year
      from tablename
      group by Place, Year
      having count(*) >= 2) dt
  on t.place = dt.place and t.year = dt.year

Upvotes: 1

Related Questions