SQL : For each ID, only display the highest value from another column (can’t group by)

I have this table here :

+---------+----------+------------+------------+
| idStep  |  idProj  | dateStart  | dateEnd    |
+---------+----------+------------+------------+
|       1 |        1 | 2011-07-01 | 2011-09-01 |
|       1 |        2 | 2012-05-01 | 2012-05-10 |
|       1 |        3 | 2011-11-01 | 2012-01-20 |
|       2 |        1 | 2011-09-02 | 2011-11-30 |
|       2 |        2 | 2012-05-11 | 2012-06-01 |
|       2 |        3 | 2012-01-21 | 2012-04-01 |
|       3 |        1 | 2011-12-01 | 2012-07-07 |
|       3 |        2 | 2012-06-02 | 2012-07-01 |
|       3 |        3 | 2012-04-02 | NULL       |
|       4 |        1 | 2012-07-08 | NULL       |
|       4 |        2 | 2012-07-01 | 2012-07-21 |
|       5 |        2 | 2012-07-22 | 2012-07-23 |
+---------+----------+------------+------------+

I need to find the current step of each project by searching for the highest idStep of each idProject without using Group By, which is where I’m completely stuck. Without GROUP BY I just cannot get there.

Basically, the output should be this :

+---------+----------+------------+------------+
| idStep  |  idProj  | dateStart  | dateEnd    |
+---------+----------+------------+------------+
|       3 |        3 | 2012-04-02 | NULL       |
|       4 |        1 | 2012-07-08 | NULL       |
|       5 |        2 | 2012-07-22 | 2012-07-23 |
+---------+----------+------------+------------+

I want to use a Query built like this

SELECT idProj,idStep
FROM table
WHERE idStep = (SELECT max(idStep) FOR EACH idProj)

I know that FOR EACH isn’t SQL, I’m only trying to make my desired query structure readable.

Answer

You want a correlated subuqery:

SELECT idProj, idStep
FROM table t
WHERE t.idStep = (SELECT max(idStep)
                  FROM table t2
                  WHERE t2.idProj = t.idProj
                 );

Advertisement

Answer