Search code examples
mysqlsqlgroup-byauto-increment

Auto-increment with Group BY

I have two tables as follows:

Contract

Contract |

Contractuser

Contractuser

My job was to fetch latest invoice date for each contract number from Contractuser table and display results. The resultant table was as follows:

Result Table

Result Table

Now I wanted to get a auto-increment column to display as the first column in my result set.

I used the following query for it:

SELECT @i:=@i+1 AS Sno,a.ContractNo,a.SoftwareName,a.CompanyName,b.InvoiceNo,b.InvoiceDate,
b.InvAmount,b.InvoicePF,max(b.InvoicePT) AS InvoicePeriodTo,b.InvoiceRD,b.ISD
     FROM contract as a,contractuser as b,(SELECT @i:=0) AS i
     WHERE a.ContractNo=b.ContractNo
     GROUP BY b.ContractNo
     ORDER BY a.SoftwareName ASC;

But it seems that the auto-increment is getting performed before the group by procedure because of which serial numbers are getting displayed in a non contiguous manner.

Solution

  • GROUP BY and variables don't necessarily work as expected. Just use a subquery:

    SELECT (@i := @i + 1) AS Sno, c.*
FROM (SELECT c.ContractNo, c.SoftwareName, c.CompanyName, cu.InvoiceNo, cu.InvoiceDate,
             cu.InvAmount, cu.InvoicePF, max(cu.InvoicePT) AS InvoicePeriodTo, cu.InvoiceRD, cu.ISD
      FROM contract c JOIN
           contractuser as b
           ON c.ContractNo = cu.ContractNo
      GROUP BY cu.ContractNo
      ORDER BY c.SoftwareName ASC
     ) c CROSS JOIN
     (SELECT @i := 0) params;

    Notes:

    • I also fixed the JOIN syntax. Never use commas in the FROM clause.
    • I also added reasonable table aliases -- abbreviations for the tables. a and b don't mean anything, so they make the query harder to follow.
    • I left the GROUP BY with only one key. It should really have all the unaggregated keys but this is allowed under some circumstances.