Solved

MySQL Row_Number group by ID

Posted on 2016-08-22
5
46 Views
Last Modified: 2016-08-23
Hello

Does anyone know how I can apply row_num partition by in MySQL?

I want to have another column, called row_gp_id, where V_id's = 13 will have row_gp_id 53 highlighted in yellow, while those circled in red will have row_gp_id = 54

mysql-group.JPG
Im using this code:

SELECT    id
        , b_id
        , v_id
        , g_id
        , @row_gp_id := @row_gp_id  +1 AS row_gp_id
		, @prev_id := v_id
FROM table_a  
, (SELECT @row_gp_id := 52) z  , (SELECT @prev_id := '') a

Open in new window


data set is:

id, b_id, v_id, g_id, group_id,
'9', '3', '13', '1', '53',
'10', '3', '13', '1', '54',
'13', '3', '13', '1', '55',
'16', '4', '7', '1', '56',
'17', '4', '7', '1', '57',
'18', '4', '7', '1', '58',
'19', '4', '7', '1', '59',
'20', '4', '7', '1', '60',
0
Comment
Question by:barkome
5 Comments
 
LVL 30

Expert Comment

by:MlandaT
ID: 41766238
Apparently not as easy as it should be. Here are some suggestions (using variables) http://blog.sqlauthority.com/2014/03/09/mysql-reset-row-number-for-each-group-partition-by-row-number/amp/
0
 
LVL 40

Expert Comment

by:Sharath
ID: 41766240
I don't see any highlighting. Do you want same row_group_id 53 for v_id = 13 and row_group_id = 54 for v_id = 7?
0
 

Author Comment

by:barkome
ID: 41766269
mysql.JPG
@Sharath, that's right where v_id = 13, row_group_id will be 53. Where v_id = 7, then row_group_id will be 54.
0
 
LVL 22

Expert Comment

by:Kim Walker
ID: 41766303
How about using a little logic to determine when to increment row_gp_id?
SELECT    id
        , b_id
        , v_id
        , g_id
        , IF(@prev_id=v_id,@row_gp_id,@row_gp_id := @row_gp_id  +1) AS row_gp_id
		, @prev_id := v_id
FROM table_a  
, (SELECT @row_gp_id := 52) z  , (SELECT @prev_id := '') a

Open in new window

1
 
LVL 40

Accepted Solution

by:
Sharath earned 500 total points
ID: 41766517
You can try with variables and logic as Kim suggested.
If your table is not very big, you can also try with sub-queries.
select id, b_id, v_id, g_id 
       ,(select count(distinct v_id) from test t1 where t1.id <= t2.id)+51 group_id 
  from test t2;

Open in new window

0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now