How to resolve this OPERATION without using GROUP BY in "SQL"

Question

I have these tables:

steps(stepId_,stepName)
stepOfProject(idProject_,stepId_,dateofStart)
Project(IdProject_,idResponsable)

I want to get the latest step in each project it is working with this query:

select stepName 
from step 
where step.step=(select max(idstep) from stepOfProject where idProject=1) ;

But it's working just for project number 1; I want to get for all projectId.

@nick I wonder if the problem is related to Microsoft SQL Server's infamous "Column 'foo' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause." error, even when the offending column has a functional dependency on a column in the `GROUP BY` clause. — Damian Yerrick, Jul 28 '15 at 03:30
@tepples: that's a defect of (earlier editions of?) the standard SQL, not simply a problem in SQL Server. That is, many other DBMS also impose that limitation, ultimately because the SQL standard says that's what should happen. — Jonathan Leffler, Jul 28 '15 at 03:54
Are you supposed to, or can you, use ORDER BY and LIMIT 1 to somehow get the result you want. Frankly, though, you should be using GROUP BY — it really isn't clear why you don't want to use the tool designed to support the job that you want doing. — Jonathan Leffler, Jul 28 '15 at 03:57
possible duplicate of [How can I SELECT rows with MAX(Column value), DISTINCT by another column in SQL?](http://stackoverflow.com/questions/612231/how-can-i-select-rows-with-maxcolumn-value-distinct-by-another-column-in-sql) — Strawberry, Jul 28 '15 at 05:21
i get it Jonathan Leffler i want just have a new way to resolve that thats why i didnt want use a regular GROUP BY . thanks a lot jo — maro, Jul 28 '15 at 05:51

Mike K · Accepted Answer · 2015-07-28T21:09:06.327

0

Try this:

   select s1.stepName, sp1.projectId
     from steps s1
     join stepOfProject sp1 on s1.stepId = sp1.stepId
left join stepOfProject sp2 on sp1.idProject = sp2.idProject
                           and sp2.stepId > sp1.stepId
    where sp2.stepId is null

What the left join is doing here is checking for the existence of another step belonging to the same project, with a higher stepId. If that record exists, then the record is excluded from the results (that's what the sp2.stepId is null does).

This leaves you with only the highest-numbered stepId for each project.

edited Jul 28 '15 at 21:09

answered Jul 28 '15 at 02:56

Mike K

486
3
7

thanks a lot guys , question for Mike K , your suggest is working 100% i think i have to try to work with JOIN directly is better but i dont know what left join do exactly in this case ? and in this case how to ad the idProject in the final result ,, thanks a lot Mike – maro Jul 28 '15 at 05:16
@maro: added explanation. To get the `projectId`, you'll have to take it from the table aliased as `sp1` (because the `sp2` result will be null). – Mike K Jul 28 '15 at 21:10
You're welcome. Please mark the answer as 'accepted' if it's solved your problem. Heck, feel free to upvote it as well if you're feeling generous. :o) – Mike K Jul 29 '15 at 20:43

score 0 · Answer 2 · answered Jul 28 '15 at 03:09

Maybe I totally don't get why you're after... and I think Nick is right -- why NOT use a group by, unless this is an academic exercise.

It would help to have some sample data an expected results, but short of that:

select
  idProjectSummary.idProject, step.step, step.stepName 
from
  step
  join (
    select idProject, max (idstep) as max_step
    from stepOfProject
    group by idProject ) as idProjectSummary on
      step.step = idProjectSummary.max_step

I haven't used MySQL in a while, but last time I used it it didn't support the with clause.

Joel Coehoorn · Answer 3 · 2015-07-28T21:25:00.167

At first I thought you were using Sql Server, where I'd do it like this:

select s.stepName, p.idProject
from step s
cross apply (select TOP 1 idProject from stepOfProject where idstep=s.step order by idstep desc) p

Alas, MySql doesn't support the APPLY operator, and so you'll have to settle for this, which does need a GROUP BY (though it's not clear why you want to avoid that):

SELECT s.stepName, p.idProject
FROM step s
INNER JOIN (SELECT idProject, MAX(idstep) as idstep FROM stepOfProject GROUP BY idProject) p ON p.idstep = s.step

How to resolve this OPERATION without using GROUP BY in "SQL"

3 Answers3