CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Sam
Sam

Reputation: 325

SQL - The first record only

I have the following segment of code in a stored procedure:

where
((Question = 'SurgicalSectionCompleted' and ChangeTo = 'True') or
(Question = 'MedicalSectionCompleted' and ChangeTo = 'True'))

However, sometimes in the table in question, there will be multiple entries where ChangeTo will be true. It is essential that I only calculate based on the FIRST record where ChangeTo is True. What SQL commands could I use to do this? Any ideas? Thanks.

The fields recorded in the table are: id, name, personNo, entryTime, question, changeFrom, ChangeTo

Upvotes: 3

Views: 88

Answers (2)

Abdul Rasheed
Abdul Rasheed

Reputation: 6729

Use the TOP 1

select TOP 1 *
from table1
where
((Question = 'SurgicalSectionCompleted' and ChangeTo = 'True') or
(Question = 'MedicalSectionCompleted' and ChangeTo = 'True'))
order by <col1>

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269993

If you want only one row returned, then use TOP 1. For the first, use ORDER BY:

select top 1 t.*
from t
where Question in ('SurgicalSectionCompleted', 'MedicalSectionCompleted') and 
      ChangeTo = 'True'
order by entryTime asc;

If you want the first for each person, then use row_number():

select t.*
from (select t.*,
             row_number() over (partition by personNo order by entryTime) as seqnum
      from t
      where Question in ('SurgicalSectionCompleted', 'MedicalSectionCompleted') and 
            ChangeTo = 'True'
     ) t
where seqnum = 1;

Upvotes: 10

Related Questions