0

We have a table named device and the data looks like below.

device_id   name    amount  status
100          abc      50    active
100          abc     100    inactive
200          xyz     150    inactive
300          pqr     100    active
400          aaa      10    active
400          bbb      20    inactive
500          hhh      30    inactive
500          qqq      40    inactive

We have to modify an existing query , a simple select query , so as to satisfy the below conditions.

  1. If we have duplicate device ids (ex: row 1 and row 2) , with at least one of them is active, then we have to select only active device id.
  2. If we have duplicate device ids which don't have any active device ids (ex: row 7 and row 8) then we can select any device id among them.
  3. If we don't have any duplicate device ids (ex: row 3 and row 4) then we have to select all the device ids.

So final result set should look like below,

  device_id      name   amount  status
    100          abc      50    active
    200          xyz     150    inactive
    300          pqr     100    active
    400          aaa      10    active
    500          hhh      30    inactive

Existing query that we have right now is as below,

select d.DEVICE_ID, d.name, d.amount
from Device d with(nolock)
to-find
  • 33
  • 3
  • Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) – Thom A Sep 19 '21 at 11:12
  • Separate question, why are you using `NOLOCK` here..? – Thom A Sep 19 '21 at 11:12
  • Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) – Charlieface Sep 19 '21 at 12:36
  • Stop splattering your code with [nolock](https://www.sentryone.com/blog/aaronbertrand/bad-habits-nolock-everywhere) – SMor Sep 19 '21 at 12:37

1 Answers1

1

You can use row_number() to select a specific row for each group:

with d as (
    select *, Row_Number() over(partition by device_id order by status) rn
    from device
)
select device_id, name, amount, status
from d
where rn=1
Stu
  • 30,392
  • 6
  • 14
  • 33