Databases
StackOverflow2013
Postsdbo
POMySQL Group returns multiple results when aggregate function is equal
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMySQL Group returns multiple results when aggregate function is equal
    primarykey
    data
    text
    <p>I am trying to obtain a list of results from a database table ('result'), however, each member may have submitted multiple results, and I wish to retrieve their best time.</p> <p>The query below works (although I'm not sure if it is efficient), except in the case where there are multiple results from the same competitor with the same time scored. (e.g. competitor #2 scores time 32.15 seconds twice). In this situation I get two rows for this competitor, rather than the one I'd like.</p> <pre><code>SELECT result_id FROM ( SELECT member_id,MIN(time) AS mintime FROM result JOIN member_result ON result.id = member_result.result_id WHERE event_id = ? GROUP BY member_id ORDER BY mintime ) AS x JOIN result ON result.time = x.mintime JOIN member_result ON member_result.result_id = result.id AND member_result.member_id = x.member_id </code></pre> <p>Any suggestions to fix the issue would be greatly received.</p> <p>EDIT: Structure of tables as requested;</p> <pre><code>member_result (TABLE) id int unsigned (10) result_id int unsigned (10) member_id int unsigned (10) club_id int unsigned (10) result (TABLE) id int unsigned (10) time decimal (6) is50mPool BIT () date date () verified BIT () verified_date timestamp () verified_comment varchar (255) hasEmailed BIT () enabled BIT () event_id int unsigned (10 </code></pre> <p>sample issue: result: id#1 for event#2, time 60 member_result: id#1, result_id 1, member_id 3 result: id#2 for event#2, time 60 member_result: id#2, result_id 2, member_id 3</p> <p>I'd just like one row returned, with either result_id - currently both rows are returned as the min time is equal.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USmctedo
    1. USmctedo
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COThis is another "least / greatest per group" question. Similar questions have been asked literally hundreds of times before on here - it's probably the most duplicated question on all of StackOverflow. Please use the search feature to find similar questions.
      singulars
      1. POMySQL Group returns multiple results when aggregate function is equal
      1. USMark Byers
    2. COI did a great deal of searching before posting my question. Indeed, it is a "least/greatest per group" question. However, in the other posts I found, there was no join operation, which here has allowed multiple records to be displayed in my scenario.
      singulars
      1. POMySQL Group returns multiple results when aggregate function is equal
      1. USmctedo
    3. COmuch better if you have included the structure of your tables, sample records and your desired result. MakeUseOf [*SQLFiddle*](http://www.sqlfiddle.com)
      singulars
      1. POMySQL Group returns multiple results when aggregate function is equal
      1. USJohn Woo
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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