Databases
StackOverflow2013
Postsdbo
POmysql speed of query - selecting max value of every 3 rows
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POmysql speed of query - selecting max value of every 3 rows
    primarykey
    data
    text
    <p>I have a table that holds price information. I need to select the max value of every three rows. EXAMPLE:</p> <pre><code>Table `daily_high` ____ _______ | ID | HIGH | | 1 | 24.65 | | 2 | 24.93 | | 3 | 26.02 | | 4 | 25.33 | | 5 | 25.16 | | 6 | 25.91 | | 7 | 26.05 | | 8 | 28.13 | | 9 | 27.07 | |____|_______| Desired output to new table (ID will be auto-increment so don't assume an association exists between this ID 1 and the daily_high ID 1: ____ ___________ | ID | 3MaxHIGH | |____|___________| | 1 | 26.02 | | 2 | 25.91 | | 3 | 28.13 | |____|___________| </code></pre> <p>I want to compare IDs 1,2, and 3 to determine the high value among them. Then once I have compared 1-3, I want to move on to 4 through 6, then 7 through 9, etc until I've done this for all values contained in the table (currently about 400,000 values). I have written code that uses</p> <p><code>SELECT max(HIGH) FROM daily_high as dh1 JOIN (SELECT max(HIGH) FROM daily_high WHERE id &gt;= dh1 AND id &lt; (dh1.id + 3))</code></p> <p>This works but is horribly slow. I've tried using the SELECT statement where I identify the column values to be pull for display, meaning between the SELECT and FROM parts of the query.</p> <p>I've tried to use JOIN to join all 3 rows onto the same table for comparison but it too is horribly slow. By slow I mean just under 10 seconds to gather information for 20 rows. This means that the query has analyzed 60 rows (20 groups of 3) in 9.65879893303 seconds (I didn't make this up, I used microtime() to calculate it.</p> <p>Anyone have any suggestions for faster code than what I've got? </p> <p>Keep in mind that my actual table is not the same as what I've posted above, but it the concept is the same.</p> <p>Thanks for any help. </p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USj_allen_morris
    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. VO
      singulars
      1. POmysql speed of query - selecting max value of every 3 rows
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POmysql speed of query - selecting max value of every 3 rows
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POmysql speed of query - selecting max value of every 3 rows
      1. This table or related slice is empty.
      1. VTUpMod
    1. COSelect the floor of the id divided by 3 and the max HIGH, grouping by the floor of the id divided by 3. If you need it for each 3 records (even if there is a gap, so the first 3 might be 1 3 and 7) then you will probably have to calculate a sequence number and use that.
      singulars
      1. POmysql speed of query - selecting max value of every 3 rows
      1. USKickstart
 

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