StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Part of the problem is that you can have multiple <code>orderLevel</code> values for each <code>fkPerson</code> so you will have to retrieve the <code>max(orderLevel)</code> for each <code>fkPerson</code> and then perform another join to get the <code>place</code>/<code>formation</code> that is associated with each person. </p> <p>I see a couple ways that you can get the result. </p> <p>You can use a subquery to get the <code>max(orderLevel)</code> and then join on your tables to return the desired columns:</p> <pre><code>select t1.fkPerson, at.MaxOrder, t1.Place, a.Formation from t_formation t1 inner join a_level a on t1.fkLevel = a.idLevel inner join ( select t.fkPerson, max(a.orderLevel) MaxOrder from t_formation t left join a_level a on t.fkLevel = a.idLevel group by t.fkPerson ) at on t1.fkPerson = at.fkPerson and at.maxorder = a.orderLevel; </code></pre> <p>See <a href="http://sqlfiddle.com/?r44b=no#!2/d4583/16" rel="nofollow">SQL Fiddle with Demo</a>.</p> <p>The other way that you could get the result would be to use a query in your <code>WHERE</code> clause to filter the rows that do not have the <code>max(orderLevel)</code>:</p> <pre><code>select t.fkperson, a.orderlevel, t.place, a.formation from t_formation t left join a_level a on t.fkLevel = a.idLevel where a.orderlevel = (select max(orderlevel) from a_level a inner join t_formation t1 on a.idlevel = t1.fklevel where t.fkperson = t1.fkperson group by t1.fkperson); </code></pre> <p>See <a href="http://sqlfiddle.com/?r44b=no#!2/d4583/17" rel="nofollow">SQL Fiddle with Demo</a>.</p> <p>As a side note, your sample data does not seem to be able to give the result that you request. The max <code>orderLevel</code> for <code>fkPerson = 2</code> is 3 not 2 so the formation will be <code>University</code> not <code>High School</code>.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload