0

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.

James Z
  • 12,209
  • 10
  • 24
  • 44
alex hale
  • 19
  • 5
  • You can't really have that work, since it does evaluate the variable for each row. What you need is a subquery to handle the grouping and then join the increment to the results. – Andrew Jan 16 '18 at 14:53
  • For future reference, it's always best to provide your data in a plain text format, as it's easier to copy/paste it if someone wants to try and replicate your source data/results. – user2366842 Jan 16 '18 at 14:53
  • +user2366842 Point noted – alex hale Jan 16 '18 at 15:29

2 Answers2

0

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.
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • I just removed the SELECT @i:=@i+1 in the FROM clause and the query worked like a charm. Thank you so much for the help – alex hale Jan 16 '18 at 15:40
0

SELECT @row_no := IF(@prev_val = lpsm.lit_no, @row_no + 1, 1) AS row_num,@prev_val := get_pad_value(lpsm.lit_no,26) LAWSUIT_NO,lpsm.cust_rm_no
FROM lit_person_sue_map lpsm,(SELECT @row_no := 0) x,(SELECT @prev_val := '') y ORDER BY lpsm.lit_no ASC;

This will return sequence number group by lit_no;

Bhabani
  • 1
  • 2