How to add a coulmn with seqeunce number

Question

I am trying to add a column to a query that will put a line number for each line with in a transaction.

STORE CODE  TRANS NUMBER    LINE NUMBER
12            1234                    1
12            1234                    2
12            1234                    3
12            2345                    1
12            2345                    2

This is my query any idea how to do this?

select sal_snum as "Store Code",sal_trn as "Transaction Number",
sal_trm as "Register",sal_dt as "TrasactionDate",sal_tm as "TranactionTime",
sum(sal_tax) as "TotalTaxAmount","" as "CustomerCode",
"" As "CustomerCardType","" as "CustomerCardNumber",
"" as "TotalPointsOnTransaction","" as "Note1","" as "Note2",
sal_emp as "Staff 1 Code", "" as "Staff 2 Code","" as "Global Tax Code",
"" as "Shipping Address Line 1","" as "Shipping Address Line 2",
"" as "Shipping Contatc Name","" as "Shipping Contact Phone",
"" as "Shipping Note","" as "Shipping Latitude",
"" as "Shippong Longitude","" as "Layaway/Order Number","" as "Layaway Status"
from sales
group by sal_snum,sal_trn,sal_trm ,sal_dt ,sal_tm,sal_emp

Possible duplicate of [ROW\_NUMBER() in MySQL](http://stackoverflow.com/questions/1895110/row-number-in-mysql) — Lukasz Szozda, Apr 04 '16 at 19:38

score 0 · Answer 1 · answered Apr 04 '16 at 19:43

You can use this. You can put your hole query in like this.

SELECT @nr := (@nr+1) AS nr,p.*
group by sal_snum,sal_trn,sal_trm ,sal_dt ,sal_tm,sal_emp
FROM (
    select sal_snum as "Store Code",sal_trn as "Transaction Number",
    sal_trm as "Register",sal_dt as "TrasactionDate",sal_tm as "TranactionTime",
    sum(sal_tax) as "TotalTaxAmount","" as "CustomerCode",
    "" As "CustomerCardType","" as "CustomerCardNumber",
    "" as "TotalPointsOnTransaction","" as "Note1","" as "Note2",
    sal_emp as "Staff 1 Code", "" as "Staff 2 Code","" as "Global Tax Code",
    "" as "Shipping Address Line 1","" as "Shipping Address Line 2",
    "" as "Shipping Contatc Name","" as "Shipping Contact Phone",
    "" as "Shipping Note","" as "Shipping Latitude",
    "" as "Shippong Longitude","" as "Layaway/Order Number","" as "Layaway Status"
    from sales
) p
CROSS JOIN ( SELECT @nr := 0) AS parameter
ORDER BY nr;

How to add a coulmn with seqeunce number

1 Answers1