CODEWITHSUNDEEP
mysqlselect
user1448031
user1448031

Reputation: 2166

MySQL SELECT row increment by group

I would like to select data from multiple tables like below. I want to have 'gID' auto increment for each group of 'dID'. The multiple tables that I am querying do not have auto increment values. So gID field has to be created on the fly.

dID     gID     bID     data
===     ===     ===     ====
400     1       20     data1
400     2       21     data2
401     1       28     data3
402     1       52     data4
402     2       74     data5
402     3       75     data6
402     4       89     data7

I am unable to figure out how to achieve this.

I tried doing this but it doesn't seem to be working:

SELECT t1.dID, @i:=@i+1 AS gID, t2.bID, t2.data
FROM table1 t1, table2 t2, (SELECT @i:=0) m
WHERE t1.mID = t2.mID
ORDER BY t1.dID

Upvotes: 1

Views: 2066

Answers (1)

Zane Bien
Zane Bien

Reputation: 23125

No need for "@" variables... Try this:

SELECT 
    a.dID, 
    COUNT(*) AS gID, 
    b.bID, 
    b.data
FROM 
    table1 a
INNER JOIN 
    table2 b ON a.mID = b.mID
INNER JOIN
    (
        SELECT a.dID, b.bID
        FROM table1 a
        INNER JOIN table2 b ON a.mID = b.mID
    ) c ON a.dID = c.dID AND b.bID >= c.bID
GROUP BY 
    a.dID,
    b.bID
ORDER BY
    a.dID,
    gID

This solution also allows bID to have duplicate values (unique per dID group).

Upvotes: 2

Related Questions