How to Add Serial No in Select Statement in Mysql Join

Question

There is two table in database.

== tblOrder ==
OrderId
Customer

== tblOrderItem ==
OrderItemId
OrderId
ItemId
Qty
Price

My Query :-

SELECT o.OrderId,o.Customer,oi.ItemId,oi.Qty,oi.Price 
FROM tblorder o
JOIN tblorderitem oi ON oi.OrderId=o.OrderId

Result :-

+---------+----------+--------+-----+-------+
| OrderId | Customer | ItemId | Qty | Price |
+---------+----------+--------+-----+-------+
|    1001 | john day |    501 |   1 |    10 |
|    1001 | john day |    502 |   2 |     9 |
|    1002 | amy gill |    201 |   5 |     2 |
|    1003 | hardcaur |    501 |   1 |    10 |
|    1003 | hardcaur |    509 |   2 |     5 |
|    1003 | hardcaur |    201 |   2 |     2 |
+---------+----------+--------+-----+-------+

I want to generate SNO and SNOI(Temp Serial No) in select statement so that result will be like this :-

+------+---------+----------+------+--------+-----+-------+
| SNO  | OrderId | Customer | SNOI | ItemId | Qty | Price |
+------+---------+----------+------+--------+-----+-------+
|    1 |    1001 | john day |    1 |    501 |   1 |    10 |
|    1 |    1001 | john day |    2 |    502 |   2 |     9 |
|    2 |    1002 | amy gill |    1 |    201 |   5 |     2 |
|    3 |    1003 | hardcaur |    1 |    501 |   1 |    10 |
|    3 |    1003 | hardcaur |    2 |    509 |   2 |     5 |
|    3 |    1003 | hardcaur |    3 |    201 |   2 |     2 |

What will be my query?

you mean row number? Check https://stackoverflow.com/questions/2520357/mysql-get-row-number-on-select — Kamran Shahid, Nov 16 '17 at 14:05
Possible duplicate of [MySQL - Get row number on select](https://stackoverflow.com/questions/2520357/mysql-get-row-number-on-select) — Kamran Shahid, Nov 16 '17 at 14:07
There are two columns in your example called `SrNo`. The question doesn't make sense. — Gordon Linoff, Nov 16 '17 at 14:08
the second SrNo is not logical, i don't understand the question — Daniel E., Nov 16 '17 at 14:09
See: [Why should I provide an MCVE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) — Strawberry, Nov 16 '17 at 14:10
Presumably TblOrder has columns OrderId, Customer and tblOrderItem has ItemId, Qty, Price? — SE1986, Nov 16 '17 at 14:41
You need to post the schema of the two tables and the sample data in them. I have noticed since my original comment that tblorderItem also has a column OrderId. — SE1986, Nov 16 '17 at 14:46
You still have't added the schema of the two tables tblOrder and tblOrderItem and what their data looks like. That's crucial information to be able to help — SE1986, Nov 16 '17 at 15:05
You can't have two columns with the same name, so let's call them SrNoOrder and SrNoItem. What you want is a cumulative count on distinct OrderId for SrNoOrder, and a cumulative count on ItemId within each OrderId for SrNoItem. Right? — itsLex, Nov 16 '17 at 15:06

score 1 · Accepted Answer · answered Nov 16 '17 at 15:22

You can use variables.

 set @ord ='';
 set @val1 =1;
 set @val2 =0;

select SR_No_1, OrderId, Customer, SR_No_2, ItemId, Qty, Price
from 
(
SELECT    t.*,
          @val1 := if(@ord=OrderId, @val1+1, 1) as SR_No_2,
          @val2 := if(@ord=OrderId,@val2, @val2+1) as SR_No_1,
          @ord := OrderId
FROM      table1 t
 ) t   
ORDER BY  orderId asc;

Result:

+---------+---------+----------+---------+--------+-----+-------+
| SR_No_1 | OrderId | Customer | SR_No_2 | ItemId | Qty | Price |
+---------+---------+----------+---------+--------+-----+-------+
|       1 |    1001 | john day |       1 |    501 |   1 |    10 |
|       1 |    1001 | john day |       2 |    502 |   2 |     9 |
|       2 |    1002 | amy gill |       1 |    201 |   5 |     2 |
|       3 |    1003 | hardcaur |       1 |    501 |   1 |    10 |
|       3 |    1003 | hardcaur |       2 |    509 |   2 |     5 |
|       3 |    1003 | hardcaur |       3 |    201 |   2 |     2 |
+---------+---------+----------+---------+--------+-----+-------+

DEMO

P.S. Kindly note that for demonstration purpose i have inserted data to one table. you can modify the query by introducing the join between 2 tables

How to Add Serial No in Select Statement in Mysql Join

1 Answers1