StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POfrom a joined table, select the max value of a column, but if there are multiples maxes, pick the one that has the max value from another column
text
Body
copied!<p>I need to join another table to my query, and grab the max value of a particular column from that joined table. The problem is that sometimes a user will have multiples of that max (eg: if the max value is 5.1, there is another row that contains that max value as well, so it gives multiple results). I need to know how to have it grab the max, and when there are multiples of that max (and ONLY when there are multiples of that max, so that I still get the results from users that don't have multiple max values), grab the max value from another column, without forgetting the max value that the query initially got.</p> <p>I've included my current query below that returns the multiple max values. The table I am joining and referring to is APPLICATION_VERSION. I need to grab the max value based off the USER_ACCOUNT_ID (which I get from the EMPLOYEE table) from column VERSION_NUMBER. If there are multiple maxes of VERSION_NUMBER, I want it to choose the max VERSION_NUMBER based on the max VERSION_CHANGE_DATE. Sometimes though there are multiples maxes of the VERSION_CHANGE_DATE also so I would then want it to pick the one with the the max VERSION_CHANGE_DATE and then the APPLICATION_VERSION_ID. </p> <p>Btw, sorry if I made this more complicated than it needs to be. Just wanted to be thorough. I'd really appreciate any assistance :)</p> <pre><code>SELECT e.user_account_id,e.employee_id,e.external_id_1,e.external_id_2,e.last_name as LAST,e.first_name as FIRST,e.job_profile_type as rank,e.status_change_date,t.name as TEAM,a.alignment_name as TERRITORY,m.machine_node_id as NODE_ID,a.alignment_id,t.division,av.version_change_date,av.version_number as EI_Version,av.login_date as LAST_LOGIN,m.platform_version FROM employee e, alignment a, machine_node m, team t, application_version av, (SELECT av.user_account_id,MAX(av.version_change_date) as maxdate,max(av.application_version_id) as maxversionid FROM application_version av GROUP BY av.user_account_id) av2 where e.employee_id = a.employee_id and av.version_change_date = av2.maxdate and e.employee_id = m.employee_id and t.team_id = a.team_id and e.status = 'ACTV' and m.status = 'ACTV' and e.user_account_id=av.user_account_id and m.machine_type = 'REMO' and e.external_id_1= 'XM68823' order by e.last_name asc </code></pre>

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