itzumar
Posts: 88
|
| Posted: 10/12/2013, 1:41 AM |
|
dear all!
i have 6 rows in mysql table as follow
city student
mzd umar
mzd ali
mzd ahsan
bagh john
bagh saad
bagh king
i want to genrate serial number for my table . but i want serial number repeated when city name is chnaged. it should look like that
city student s.no
mzd umar 1
mzd ali 2
mzd ahsan 3
bagh john 1
bagh saad 2
bagh king 3
_________________
Umar |
 |
 |
Fishertoy
Posts: 9
|
| Posted: 10/17/2013, 1:53 PM |
|
Hello, first thing is that you need to add an autonumeric field being a primary key for the table (id in this example).
then just use this query.
SELECT
my_table.id,
my_table.city,
my_table.student,
(SELECT
COUNT(*)
FROM
my_table t2
WHERE
t2.city = my_table.city AND
t2.id <= my_table.id) sno
FROM
my_table
regards
|
|
|
|
itzumar
Posts: 88
|
| Posted: 10/18/2013, 4:57 AM |
|
Dear fisher boy!
lot of thanks for your support. you almost solve my problem. but the problem is what can i do if city was not in order. for example if
city student s.no
mzd umar 1
bagh ali 1
mzd ahsan 2
bagh john 2
bagh saad 3
mzd king 3
than what i do? u almost solve 95%. plz guide me further
_________________
Umar |
|
|
|
Fishertoy
Posts: 9
|
| Posted: 10/18/2013, 8:44 AM |
|
SELECT
my_table.id,
my_table.city,
my_table.student,
(SELECT
COUNT(*)
FROM
my_table t2
WHERE
t2.city = my_table.city AND
t2.id <= my_table.id) sno
FROM
my_table
ORDER BY
my_table.city,
my_table.id
Regards
|
|
|
|
|
