SQL Query filtering records

Re-posting for clarity

I need a query to filter out records that have identical values in the Project and Address fields where Status has 'In Progress' and 'Complete' so only the 'Complete' row is returned for those records.

Goal: When there are records with the same project and address with a status of both 'In Progress' and 'Complete' to eliminate the 'In Progress' records for that Project/Address.


Existing Table
ID    Project     Address     Status         Units
1        House1      Addr 1      In Progress     15
2        House1      Addr 1      In Progress       20
3        House1      Addr 1      In Progress     33
4        House1      Addr 1      Complete        42
5        House1      Addr 1      Complete        12
6        House1      Addr 1      Complete        60
7        House2      Addr 2      In Progress     44
8        House2      Addr 2      In Progress     12
9        House2      Addr 2      In Progress     65
10       House2      Addr 2      Complete        87
11       House2      Addr 2      Complete        36
12       House2      Addr 2      Complete        45
13       House3      Addr 3      In Progress     84
14       House3      Addr 3      In Progress     21
15       House4      Addr 4      Complete        19
16       House4      Addr 4      Complete        74

Should Return:
ID    Project     Address     Status         Units
4        House1      Addr 1      Complete        42
5        House1      Addr 1      Complete        12
6        House1      Addr 1      Complete        60
10       House2      Addr 2      Complete        87
11       House2      Addr 2      Complete        36
12       House2      Addr 2      Complete        45
13       House3      Addr 3      In Progress     84
14       House3      Addr 3      In Progress     21
15       House4      Addr 4      Complete        19
16       House4      Addr 4      Complete        74
Sandman_19Asked:
Who is Participating?
 
awking00Commented:
I posted this response in your other question, which is basically a duplicate of this one. You should request a moderator to remove one of them.

select id, project, address, status,units from
(select id, project, address, status,units,
 dense_rank() over (partition by project, address order by status) rnk
 from existing_table) as x
where rnk = 1
order by id;
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
which version of sql server are you using ?
0
 
Sandman_19Author Commented:
2005
0
 
Sandman_19Author Commented:
Perfect.

Thanks.
0
 
Sandman_19Author Commented:
Perfect.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.