CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
user1141584
user1141584

Reputation: 619

Return results where first entry is 1 and all subsequent rows are 0

I m working on weird SQL query

 Patient_ID    Count    order_no
 1               1          1
 2               1          2  
 2               0          3
 2               0          4
 3               1          5
 3               0          6

where I need to count the patient as above, for every new patient , the count column is 1. If repeated , the below entry it should be 0

I m confused how should make that work in SQL

Upvotes: 0

Views: 155

Answers (1)

cgatian
cgatian

Reputation: 22994

In order to make the first entry 1 and all subsuqent entries 0, I believe you need a ranking with partition by the order number. Please checkout the sqlfiddle below to test results.

http://www.sqlfiddle.com/#!3/4e2e2/17/0

SELECT
patient_id
,CASE WHEN r.rank = 1 
 THEN 1
 ELSE 0
 END
, order_number
FROM
(
  SELECT 
  order_number
  ,patient_id
  ,ROW_NUMBER() OVER (PARTITION BY patient_id ORDER BY order_number)[rank] 
  FROM 
  PatientTable
)r

Upvotes: 7

Related Questions