title: Need some SQL advice with selecting max or Top 1 Record
body: <p>I'm trying to edit a Crystal Reports SQL Expression where the original designer used the following SQL Expression to pull notes from a drive:</p>
  
  <pre><code>(select max(Text) from rpt_NotesDetail 
   where DriveID="DriveMaster"."DriveID" and
          Reason in (select codeid 
                     from rpt_QuickCodes 
                     where DescShort like N'Publicity'))
  </code></pre>
  
  <p>We don't want it to pull max(Text), we are looking for it to grab the latest (or most recent) note in the NotesDetail table based on the createdate field.</p>
  
  <p>I have tried two ways to handle this and I'm getting an error on both:</p>
  
  <pre><code>(select (Text) from rpt_NotesDetail 
   where DriveID="DriveMaster"."DriveID"
   and createdate = (select max(createdate) 
                     from rpt_notesdetail 
                     where DriveID="DriveMaster"."DriveID")
                     and Reason in (select codeid 
                                    from rpt_QuickCodes 
                                    where DescShort like N'Publicity'))
  
  
  (select (Text) 
   from rpt_NotesDetail 
    where DriveID="DriveMaster"."DriveID"
      and createdate = (select top 1 createdate 
                        from rpt_notesdetail 
                        where DriveID="DriveMaster"."DriveID" 
                        order by "drivemaster"."fromdatetime" desc)
      and Reason in (select codeid 
                     from rpt_QuickCodes 
                     where DescShort like N'Publicity'))
  </code></pre>
  
  <p>Both expressions are giving me this error:
  Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, &lt;, &lt;=, >, >=, or when the subquery is used as an expression.</p>
  
  <p>Not sure what would be the cause of this error, any suggestions?</p>