Question about a school project

Transact-SQL
1

Hello,
for a school project, I have to extract data to get the following table :
idEtape 4 - idProject 1
idEtape 5 - idProject 2
idEtape 3 - idProject 3

So, I have to set the highest "idEtape" for each "idProject", probably with sub queries and MAX, but after trying and searching online I can't get any answers. Can anyone help me with that ? Thank you

etapexprojet
etapexprojet346×935 23.1 KB

2 
SELECT MAX(idEtape) AS idEtape, idProject
FROM EtapexProjet
GROUP BY idProject
3

Thank you ! But I forgot to mention my teacher does not want me to use the GROUP BY function, is there another way to get things done ?

4

Not sure what you teacher is looking for - but this can be done using ROW_NUMBER(), RANK() or DENSE_RANK().

Try using one of those and if you get stuck - let us know.

Hint: use of these will require either a CTE or a derived table to allow you to then filter on the row/rank returned.

5

You can use a subquery, but you'll need a distinct to avoid getting duplicate rows back. I don't know if you're studied DISTINCT or not, of course.

SELECT DISTINCT 
    (SELECT MAX(idEtape) AS idEtape FROM EtapexProjet EP2 
     WHERE EP2.idProject = EP.idProject) AS idEtape, 
    EP.idProject
FROM EtapexProjet EP
6

Thank you, it seems to be working ! And yes, I have learned about DISTINCT, I'm just having a really hard time understanding subqueries with all the renaming "AS".

Also, this question was a part of a bigger question.
I have these 2 tables (sorry it's in french, but it should not matter that much)

test
test723×1162 17.9 KB

So the question I have to answer is "Give idprojet, nometape, livrable for the highest idetape of every idprojet without using a GROUP BY". The answer you gave me worked for the "highest idetape of every idprojet". Now, I tried to do a JOIN like this :
SELECT idprojet, nometape, livrable
-> FROM etape
-> JOIN etapexprojet
-> ON etape.idetape= etapexprojet.idetape
-> WHERE (SELECT DISTINCT
-> (SELECT MAX(idEtape) AS idEtape FROM EtapexProjet EP2
-> WHERE EP2.idProjet = EP.idProjet) AS idEtape,
-> EP.idProjet
-> FROM EtapexProjet EP);

And I get the error 1241 : Operand should countain 1 column(s).

I've read online that I cannot select more than 1 column. I tried to remove the EP.idProjet in the inner query but I get the same error. What am I doing wrong here ?