CODEWITHSUNDEEP
mysqlsql
Tiny
Tiny

Reputation: 27899

Getting a row number of a row which is associated with a specific group of rows

I have a table in MySQL database as shown in the following snap shot.

enter image description here

I need to obtain a current row number based on rating_id (primary key) given in each group of products.

Currently the following SQL obtains a row number based on rating_id from all rows in the table.

SELECT rownum 
FROM   (SELECT @rownum := @rownum + 1 AS rownum, 
               tbl.rating_id 
        FROM   rating tbl, 
               (SELECT @rownum := 0)t 
        ORDER  BY tbl.rating_id DESC)t 
WHERE  rating_id = ?

How to restrict this query to a specific group of products (prod_id)?

In other words, how to get a row number from a group of rows which is specific a particular product (prod_id)?

Upvotes: 2

Views: 85

Answers (1)

valex
valex

Reputation: 24134

I guess you need to count each group of PROD_ID separately. In this case you should add one more user defined variable to store previous PROD_ID and reset @rownum when new PROD_ID group starts.

SELECT rownum,rating_id,prod_id 
FROM   (SELECT if(prod_id=@prev_prod,@rownum := @rownum + 1,@rownum:=1)
               AS rownum, 
               tbl.rating_id,
               tbl.prod_id,
               @prev_prod:=prod_id  
        FROM   rating tbl, 
               (SELECT @rownum := 1,
                       @prev_prod:=NULL)t 
        ORDER  BY tbl.prod_id)t 
WHERE  rating_id = ?

SQLFiddle demo

Upvotes: 2

Related Questions