• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 812
  • Last Modified:

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
0
Sandman_19
Asked:
Sandman_19
  • 3
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
which version of sql server are you using ?
0
 
Sandman_19Author Commented:
2005
0
 
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
 
Sandman_19Author Commented:
Perfect.

Thanks.
0
 
Sandman_19Author Commented:
Perfect.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now