selecting only those "best" results that meet specified criteria

• Andy Freeman

  SSC Enthusiast

  Points: 110

  More actions

  September 24, 2004 at 2:24 pm

  #161540

  I have a table, faults, with three data columns.  The first two data columns, eventId

  and causeId, are primary keys for other tables, causes and events respectively.  The

  third column is the probability that a given cause is responsible for the corresponding

  event.

  I'm trying to find the eventIds for which the most likely cause has specified characteristics.

  My "best" attempt, using "select top 1", returns all 'banana peel' events.  (The

  "f.causeId = (select top 1 ...)" clause is basically an expensive no-op.)

      select f.eventId

      where (f.causeId = c.causeId)

        and (c.name = 'banana peel')

        and (f.causeId = (select top 1 t.causeId

                          where (f.eventId = t.eventId)

                          from faults t

                          orderby t.probability desc))

      from faults f, causes c

  How do I select events for which 'banana peel' is the most likely cause?

• KarlTheBaker

  Newbie

  Points: 9

  More actions

  September 25, 2004 at 8:55 am

  #524421

  I think this will perform better.

  SELECT Distinct F.EventID, CauseID

     FROM Faults F INNER JOIN (SELECT MAX(Probability) MaxProb, EventID

        FROM Faults GROUP BY EventID) MP

        ON (MP.MaxProb=F.Probability and MP.EventID=F.EventID)

     WHERE CauseID='Banana Peel'

  -Karl