Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2010
    Posts
    26

    Unanswered: How to write the record number on a separate field

    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

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    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

  3. #3
    Join Date
    Jan 2010
    Posts
    26
    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:
    1
    2
    2
    4
    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
    Last edited by telmessos; 02-11-10 at 10:49.

  4. #4
    Join Date
    Jan 2010
    Posts
    26
    no other people can help me ?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •