Databases
StackOverflow2013
Postsdbo
POwhy is this MySQL query producing the wrong row numbers?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POwhy is this MySQL query producing the wrong row numbers?
    primarykey
    data
    text
    <p>given the following query</p> <pre><code>SET @num :=0, @current_shop_id := NULL, @current_product_id := NULL; SELECT * FROM ( SELECT products.shop_id, products.product_id, @num := IF(@current_shop_id=shops.shop_id, IF(@current_product_id=products.product_id,@num,@num+1),0) AS row_number, @current_shop_id := shops.shop_id AS shop_dummy, @current_product_id := products.product_id AS product_dummy FROM favorites fav1 INNER JOIN products ON fav1.product_id=products.product_id AND fav1.current=1 AND fav1.closeted=1 AND fav1.user_id=30 INNER JOIN shops ON shops.shop_id = products.shop_id ORDER BY shops.shop ASC, products.product_id DESC ) AS rowed_results WHERE rowed_results.row_number&gt;=0 AND rowed_results.row_number&lt;(20) AND shop_id=130 </code></pre> <p>I am expecting row_number values of the form</p> <pre><code>+---------+------------+------------+------------+---------------+ | shop_id | product_id | row_number | shop_dummy | product_dummy | +---------+------------+------------+------------+---------------+ | 130 | 1153746 | 0 | 130 | 1153746 | | 130 | 1153736 | 1 | 130 | 1153736 | | 130 | 1139944 | 2 | 130 | 1139944 | | 130 | 1098296 | 3 | 130 | 1098296 | | 130 | 1017455 | 4 | 130 | 1017455 | | 130 | 551953 | 5 | 130 | 551953 | | 130 | 551914 | 6 | 130 | 551914 | +---------+------------+------------+------------+---------------+ </code></pre> <p>(ie, all unique product_id values associated with a given shop_id value get a unique row number, starting from 0). Instead, I am getting</p> <pre><code>+---------+------------+------------+------------+---------------+ | shop_id | product_id | row_number | shop_dummy | product_dummy | +---------+------------+------------+------------+---------------+ | 130 | 1153746 | 1 | 130 | 1153746 | | 130 | 1153736 | 0 | 130 | 1153736 | | 130 | 1139944 | 0 | 130 | 1139944 | | 130 | 1098296 | 0 | 130 | 1098296 | | 130 | 1017455 | 0 | 130 | 1017455 | | 130 | 551953 | 1 | 130 | 551953 | | 130 | 551914 | 0 | 130 | 551914 | +---------+------------+------------+------------+---------------+ </code></pre> <p>what am I doing wrong?</p> <p>EDIT:</p> <p>Michael Berkowski's solution (use three queries, one for retrieving data, one for adding row numbers, one for limiting by row) works (syntax slightly changed):</p> <pre><code>SET @num :=0, @current_shop_id := NULL, @current_product_id := NULL; #limit by row SELECT * FROM ( #add row SELECT limit_query.*, @num := IF(@current_shop_id=shop_id, IF(@current_product_id=product_id,@num,@num+1),0) AS row_number, @current_shop_id := shop_id AS shop_dummy, @current_product_id := product_id AS product_dummy FROM ( #retrieve data SELECT row_query.* FROM ( SELECT products.shop_id, products.product_id FROM favorites fav1 INNER JOIN products ON fav1.product_id=products.product_id AND fav1.current=1 AND fav1.closeted=1 AND fav1.user_id=30 INNER JOIN shops ON shops.shop_id = products.shop_id ) AS row_query ORDER BY shop_id ASC, product_id DESC ) AS limit_query ) AS rowed_results WHERE rowed_results.row_number&gt;=0 AND rowed_results.row_number&lt;(20) AND shop_id=130; </code></pre> <p>Purely as a matter of educational interest, I'm wondering why it is not possible to add the row numbers and limit by row number at the same time, as in the following (non-functional) example</p> <pre><code>SET @num :=0, @current_shop_id := NULL, @current_product_id := NULL; #limit by row SELECT rowed_results.*, @num := IF(@current_shop_id=shop_id, IF(@current_product_id=product_id,@num,@num+1),0) AS row_number, @current_shop_id := shop_id AS shop_dummy, @current_product_id := product_id AS product_dummy FROM ( #retrieve data SELECT row_query.* FROM ( SELECT products.shop_id, products.product_id FROM favorites fav1 INNER JOIN products ON fav1.product_id=products.product_id AND fav1.current=1 AND fav1.closeted=1 AND fav1.user_id=30 INNER JOIN shops ON shops.shop_id = products.shop_id ) AS row_query ORDER BY shop_id ASC, product_id DESC ) AS rowed_results WHERE row_number&gt;=0 AND row_number&lt;(20) AND shop_id=130; </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USjela
    1. USjela
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POwhy is this MySQL query producing the wrong row numbers?
      1. This table or related slice is empty.
      1. VTUpMod
    1. COI guess the fact that you are using `@num := ` in your select doesn't help
      singulars
      1. POwhy is this MySQL query producing the wrong row numbers?
      1. UScheesemacfly
    2. COWhy do you need this `IF` to calculate this `ro_number`? From the `row_number` values that you are expecting `@num := @num + 1` will give you the exact values that you are looking for.
      singulars
      1. POwhy is this MySQL query producing the wrong row numbers?
      1. USMahmoud Gamal
    3. CO@MahmoudGamal you are correct, given the example. this is a simplified version of a more complicated query in which it is possible for multiple rows to have the same product_id value, requiring the conditional (since only rows with unique product_id values should have unique row_number values).
      singulars
      1. POwhy is this MySQL query producing the wrong row numbers?
      1. USjela
 

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