Help selecting groups based on criteria 1

[teach314]

I am fairly new to SQL. My question is easy to solve using DAO recordsets,
but I'd like to know how SQL would do it.

In the table below, the pk is (ID, Step). For each different ID, the 4 RESULT values are distinct.

I want to find the [bold]ID values where (RESULT when STEP = 3) > (RESULT when STEP = 0). [/bold]

  ID    STEP     RESULT
  101     0       469
  101     1        32
  101     2       910
  101     3       512

  102     0       322
  102     1       566
  102     2        19
  102     3       417

  103     0       800
  103     1       127
  103     2       577
  103     3       469

  104     0        99  
  104     1       501
  104     2        32
  104     3       104

  105     1       etc.

The result should look like

  ID
  101
  102
  104

many thanks

 

[PHV]

ID values where (RESULT when STEP = 3) > (RESULT when STEP = 0).

SELECT A.ID
FROM yourTable A INNER JOIN youtTable B ON A.ID=B.ID
WHERE A.STEP=3 AND B.STEP=0 AND A.RESULT>B.RESULT

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[teach314]

Perfect. Thanks, PHV.