JOIN with GROUP BY



  • 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?



  • 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




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2