>Hoping someone can help me tweak this query. We have two tables, the meeting and the meetproc table, for each case there is 1 meeting record, and there is at least 1 meetproc record, but there could be multiple meetproc records. The primary meeproc record is the one with the lowest sysorder value. Typically they are sequential (1,2,3), but depending on edits that may have occurred, they may start with a number other than 1 such as (3,4,5).
>
>The part I need help with is determining which is the primary, in the query below I've just got a place holder for it. Anybody have an idea how I can determine if it the primary (lowest sysorder) for that CaseID?
>
>Thanks
>
>
>

>Select meetproc.procID,coPerID,
>         (Select description from dbo.coProc where dbo.coProc.ProcID=meetproc.ProcID) as Procdesc,
>         Cast(0 as bit) as isPrimary,
>         meetings.meetingnumber as CaseID,
>	 meetproc.Sysorder
>from dbo.meetings inner join
>     dbo.meetproc on dbo.meetings.meetingnumber=dbo.meetproc.Meetingnumber
>where meetings.deptID=@DeptID
>and   meetings.iscompleted=1
>and   meetings.begintime between @begintime and @EndTime
>order by meetings.meetingnumber,sysorder
>

>
>Sample result would be:
>

>ProcID    coPerID      ProcDesc             isPrimary     CaseID       Sysorder
>-------------------------------------------------------------------------------------------------------------
>9434        1241       Peripharal Angio         1              165390         1
>9461        1241       PTA Lower Extr          0              165390         2
>9485        1241       Other Proc                 0              165390         3
>

Select meetproc.procID,coPerID,
         (Select description from dbo.coProc where dbo.coProc.ProcID=meetproc.ProcID) as Procdesc,
         Cast(0 as bit) as isPrimary,
         meetings.meetingnumber as CaseID,
	 meetproc.Sysorder,
         CASE WHEN meetproc.Sysorder = Tbl1.Sysorder
                   Then 'Primary'
              ELSE 'Next pone' END AS Test    
from dbo.meetings
inner join dbo.meetproc on dbo.meetings.meetingnumber=dbo.meetproc.Meetingnumber
inner join (SELECT Meetingnumber, MIN(Sysorder) AS Sysorder
                   FROM dbo.meetproc
            GROUP BY Meetingnumber) Tbl1
      on dbo.meetings.meetingnumber=Tbl1.Meetingnumber
where meetings.deptID=@DeptID
and   meetings.iscompleted=1
and   meetings.begintime between @begintime and @EndTime
order by meetings.meetingnumber,sysorder

NOT TESTED!