JOIN with GROUP BY

[Raziyah00]

I've got a creative problem here: we need to create some JOIN's enquiries in one request, so that each subsequent JOIN can refer to the result of the previous JOIN's request. This strange design should look like this:

select a1.id, a2.param1, a3.param2, a4.param3 from sometable a1
inner join
(select id, max(param1) as param1 from a1 group by id) a2
on (a2.id=a1.id and a2.param1=a1.param1)
inner join
(select id, max(param2) as param2 from a2 group by id) a3
on (a3.id=a1.id and a3.param2=a2.param2)
inner join
(select id, max(param3) as param3 from a3 group by id) a4
on (a4.id=a1.id and a4.param2=a3.param2)
order by id

However, SQL, alas, does not allow me to rely on the sample, say, a4 on the sample results with alas a3. The result I can, of course, be achieved by setting up three drinks, referring consecutively to another one, but it's not the most elegant solution. How would that be more beautiful?

[Bogopo]

Use it. https://technet.microsoft.com/en-us/library/ms190766(v=sql.105).aspx - it's, like, "external" names. Other CTEs declared above may be referred to from CTE:

WITH 
a2 AS
 (select id, max(param1) as param1 from a1 group by id),
a3 AS
 (select id, max(param2) as param2 from a2 group by id),
a4 AS
 (select id, max(param3) as param3 from a3 group by id)
select a1.id, a2.param1, a3.param2, a4.param3 from sometable a1

inner join a2

on (a2.id=a1.id and a2.param1=a1.param1)

inner join a3

on (a3.id=a1.id and a3.param2=a2.param2)

inner join a4

on (a4.id=a1.id and a4.param2=a3.param2)

order by id