Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>You could use a MySQL variable. Eg:</p> <pre><code>SET @ttt = 0; SELECT @ttt := IF( `company_code`=(SELECT `company_code` FROM `range` r2 WHERE r2.`period_month`&lt;r1.`period_month` ORDER BY `period_month` DESC LIMIT 1), @ttt, @ttt+1) iter, `period_month`, `company_code` FROM `range` r1 WHERE emp_code = 'NIK001' ORDER BY period_month ASC; </code></pre> <p>On my test data this gives me:</p> <pre><code>iter period_month company_code 1 1 Alpha 1 2 Alpha 1 3 Alpha 2 4 Beta 2 5 Beta 2 6 Beta 3 7 Alpha 3 8 Alpha 3 9 Alpha 3 10 Alpha </code></pre> <p>The idea is that on every line you check if the current <code>company_code</code> is equal to that of the previous record. If it's different, increase the variable.</p> <p>That's a step forward. But how to group the rows? You can't group them directly, but you need to wrap that query with a second one.</p> <pre><code>SET @ttt = 0; SELECT `company_code`,MIN(`period_month`),MAX(`period_month`) FROM ( SELECT @ttt := IF( `company_code`=(SELECT `company_code` FROM `range` r2 WHERE r2.`period_month`&lt;r1.`period_month` ORDER BY `period_month` DESC LIMIT 1), @ttt, @ttt+1) iter, `period_month`, `company_code` FROM `range` r1 WHERE emp_code = 'NIK001' ORDER BY period_month ASC ) subreq GROUP BY iter ; </code></pre> <p>That's using the result from the inner query, in the outer query. This gives me, with my test data,</p> <pre><code>company_code MIN(`period_month`) MAX(`period_month`) Alpha 1 3 Beta 4 6 Alpha 7 10 </code></pre> <p>If you need to limit the selection of rows, do so in a where clause in the <em>inner</em> query, or else the whole database will be loaded into memory on each query. Another gotcha is that the inner query is fetching the previous record as "the record that has the closest lower <code>period_month</code>. As an effect, you must <em>not</em> sort the inner query by anything but <code>period_month ASC</code>. You can still sort things in the outer query if you want.</p> <p>Finally, <strong>if</strong> you're using the old <code>mysql_query</code> interface for some reason, you need to put the <code>SET</code> line in a separate query, as it can only handle one query at a time.</p> <pre><code>mysql_query("SET @ttt = 0;"); $rs=mysql_query("SELECT `company_code`,MIN(`period_month`),MAX(`period_month`) FROM ( SELECT @ttt := IF( `company_code`=(SELECT `company_code` FROM `range` r2 WHERE r2.`period_month`&lt;r1.`period_month` ORDER BY `period_month` DESC LIMIT 1), @ttt, @ttt+1) iter, `period_month`, `company_code` FROM `range` r1) subreq GROUP BY iter ;"); </code></pre> <p>Not sure how well this method works out when scaled up to large databases, but it definitely works. Maybe there's a "good" way. Just make sure you don't accidentally mess something up in the query, in case you don't fully understand it. :)</p>
    singulars
    1. This table or related slice is empty.
    1. POmysql group by using the range record value
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USnitro2k01
    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. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. CO+1 @nitro2k01 the idea of the iterator column was brilliant.
      singulars
      1. PO
      1. USEdwin Dalorzo
    2. COthanks for the answer
      singulars
      1. PO
      1. USyuda
 

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