5 Replies Latest reply on Feb 8, 2013 9:42 AM by greglane

    ExecuteSQL: Selecting one of duplicate IDs

    jbrown

      Hey all.

      I have an executeSQL statement that is pulling a list of student IDs and Consequences from a detention table. Here's what it is pulling:

      668146,ACP

      668146,WHC

       

      This one student has two different consequences, but i really only need the first one to be selected to put on a list. Some students have two of the same consequence, which looks like this:

      706685,WHC

      706685,WHC

       

      How do I structure the SQL statement to only select the ACP consequence in the first example as well as only one of the 2nd example? I realize the SELECT DISTINCT would pull only one of the 2nd example, but it would pull BOTH of the 1st example since they are technically different rows.

       

      Here's the sql statement:

      Let (TodaysDate = Get(CurrentDate);

       

       

      ExecuteSQL("

      SELECT fk_StudentID, EventType

      FROM BehaviorTable

      WHERE EventDate =?

      AND School =?

      ORDER BY fk_StudentID, EventType";

      "";"";

      TodaysDate;"KSPA"

      )

      )

       

      Thanks for your advise.