How to write the record number on a separate field

telmessos · #1 (**permalink**) 02-10-10, 02:59

Hi all,

I have table with some multiple records for each member. I need to write x th record from the same memberID on a separate field.

Let's say I have the fields of recID and memberID on the database. After my query a third field must be added and it should look like below:
Code:
Code:

Code:

recID      memberID     qnumber
1            1245         1
2            1245         2
3            1245         3
4            1530         1
5            1530         2

So basicly I have to write x th record number of each member ID. I hope I could explain it.

Thanks
telmessos

mike_bike_kite · #2 (**permalink**) 02-10-10, 07:00

I think this will work:

Code:

select recID, memberID, 
       (select count(*) from MyTable t2 
       where t2.memberID=t1.memberID and t2.recID <= t1.recID ) as qnumber
from   MyTable t1
order by recID, memberID

telmessos · #3 (**permalink**) 02-11-10, 09:04

Thanks mike,

I integrated the query you gave to me to my existing tables.

but at the following section I had a problem.

where t2.memberID=t1.memberID and t2.recID <= t1.recID

basicly I changed the "t2.recID <= t1.recID" section to "t2.islemtarihi <= t1.islemtarihi" which is the recording date and it works perfectly but on the records which has the same date it repeats the same number like:

Code:

to prevent the repeats, I need to keep the islemtarihi order, plus add a recID section. So basicly it will check the islemtarihi first in the order if the islemtarihi is same date, then it will use smaller recID.

Is this possible?

I will be glad if you could help.

Thanks
telmessos

telmessos · #4 (**permalink**) 02-15-10, 09:05

no other people can help me ?

Internet Services

Web Development

Digital Marketing

Consumer Tech