Select an ID if count is equal to 1

Question

I am trying to write a query which needs to find an ID number from 3 WHERE values based on the result only being equal to 1.

So say i want to find a patient's ID and my where clause matches the firstname, lastname and DOB. If there are 2 results because of duplicates, i need the output to be NIL else it should return the patient ID.

if(select count(*)
from patient
where last_name = 'JAMES' 
and first_name = 'JONES' 
and birth_DtTM = '1980-01-01') > 1
print 'NULL' else return Pat_ID1

This is kind of what i am leading towards.

Thanks guys

possible duplicate http://stackoverflow.com/questions/63447/how-do-you-perform-an-if-then-in-an-sql-select — Varun Nath, Aug 01 '14 at 04:27
@nathvarun disagree, because using a case expression and a subselect is probably *not* a good solution to this problem even if it's what OP is asking for. — hobbs, Aug 01 '14 at 04:30

score 0 · Answer 1 · answered Aug 01 '14 at 05:09

0

select case when  count(*)> 1
then 'NULL' else Pat_ID1 end 
from patient
where last_name = 'JAMES' 
and first_name = 'JONES' 
and birth_DtTM = '1980-01-01'
group by Pat_ID1

answered Aug 01 '14 at 05:09

Amir Keshavarz

3,050
1
19
26

score 0 · Answer 2 · answered Aug 01 '14 at 05:17

try below.

;WITH CTE(Pat_ID1,last_name,first_name,birth_DtTM,dup_rows)
as
(
    SELECT Pat_ID1,last_name,first_name,birth_DtTM,ROW_NUMBER() OVER(PARTITION BY last_name,first_name,birth_DtTM ORDER BY Pat_ID1) AS dup_rows FROM patient
)
SELECT 
case when dup_rows>1 then null
            when dup_rows=1 then Pat_ID1
            end
 FROM CTE

score 0 · Answer 3 · answered Aug 01 '14 at 08:19

You can do it like this:

SELECT
  PatientID = CASE COUNT(*) WHEN 1 THEN MAX(Pat_ID1) END
FROM
  patient
WHERE
  last_name = 'JAMES' 
  AND first_name = 'JONES' 
  AND birth_DtTM = '1980-01-01'
;

The CASE expression will evaluate either to the single Pat_ID1 matching the request or to NULL (if COUNT(*) is anything but 1).

As you can see, the Pat_ID1 value is obtained with the help of an aggregate function (by the way, you can use MIN instead of MAX just as well). This is because the presence of COUNT(*) in the query automatically implies grouping and now, if you want to reference columns of the underlying row set, you must only access their aggregated values.

Select an ID if count is equal to 1

3 Answers3