-1

I need help to write a SQL query to group all records for a particular person by the PersonId, LocationId and Role then choose the first IsActive = true record if it exists, otherwise, return the first IsActive = false. The table primary key is the PersonLocationId. There is a bunch of other columns that I also need to return for the result that is not there. Any suggestions?

| PersonLocationId | PersonId | LocationId | Role        | IsActive |
| ---------------- | -------- | ---------- | ----------- | -------- |
| 1                | 1        | 222        | StorePerson | True     |
| 2                | 1        | 222        | StorePerson | False    |
| 3                | 1        | 222        | StorePerson | False    |
Parisa.H.R
  • 3,303
  • 3
  • 19
  • 38
niroice
  • 163
  • 1
  • 14

1 Answers1

3

If I understand you correctly, you need to number the rows using ROW_NUMBER() with the appropriate PARTITION BY and ORDER BY clauses:

Statement:

SELECT PersonLocationId, PersonId, LocationId, Role, IsActive
FROM (
   SELECT 
      *,
      ROW_NUMBER() OVER (
         PARTITION BY PersonId, LocationId, Role
         ORDER BY CASE WHEN IsActive = 'True' THEN 0 ELSE 1 END, PersonLocationId
      ) AS Rn
   FROM (VALUES
      (1, 1, 222, 'StorePerson', 'True'),
      (2, 1, 222, 'StorePerson', 'False'),
      (3, 1, 222, 'StorePerson', 'False'),
      (4, 2, 222, 'StorePerson', 'False'),
      (5, 2, 222, 'StorePerson', 'False')
   ) v (PersonLocationId, PersonId, LocationId, Role, IsActive)
) t
WHERE t.Rn = 1

Result:

PersonLocationId PersonId LocationId Role        IsActive
---------------------------------------------------------
1                1        222        StorePerson True
4                2        222        StorePerson False
Zhorov
  • 28,486
  • 6
  • 27
  • 52