SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member
    Join Date
    Feb 2010
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How to write the record number on a separate field

    Hi all,

    I have the table structure of

    Code:
    CREATE TABLE /*!32312 IF NOT EXISTS*/ `bilgiler` (
      `kayitID` int(11) NOT NULL AUTO_INCREMENT,
      `hak_sahibi_id` bigint(15) DEFAULT NULL,
      `cinsiyet` tinyint(1) DEFAULT NULL,
      `dogumtarihi` date DEFAULT NULL,
      `islemtarihi` date DEFAULT NULL,
      `tanikodu` varchar(10) DEFAULT NULL,
      `tedavi_ili` tinyint(3) DEFAULT NULL,
      `dogum_ili` tinyint(3) DEFAULT NULL,
      `ikamet_ili` tinyint(3) DEFAULT NULL,
      PRIMARY KEY (`kayitID`)
    ) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin5;
    on a table and I have the following SQL query which creates a field as qnumber and writes the record number according to the islemtarihi(date).

    Code:
    Select bilgiler.hak_sahibi_id,bilgiler.cinsiyet,date_format(bilgiler.dogumtarihi,'%d.%m.%Y') as dtarihi,date_format(bilgiler.islemtarihi, '%d.%m.%Y') as itarihi,(To_days( bilgiler.islemtarihi ) - TO_DAYS( bilgiler.dogumtarihi )) as difference,bilgiler.tanikodu,bilgiler.dogum_ili,bilgiler.ikamet_ili,bilgiler.tedavi_ili,b.repeat,(select count(*) from bilgiler t2 where t2.hak_sahibi_id=bilgiler.hak_sahibi_id and t2.islemtarihi <= bilgiler.islemtarihi) as qnumber from bilgiler INNER JOIN ( SELECT hak_sahibi_id,year(islemtarihi) AS yyyy,COUNT(*) AS 'repeat' from bilgiler group by hak_sahibi_id,year(islemtarihi) ) as b on b.hak_sahibi_id = bilgiler.hak_sahibi_id and b.yyyy = year(bilgiler.islemtarihi) order by bilgiler.hak_sahibi_id,bilgiler.islemtarihi
    My problem with the query is, if there are more than one records on the same islemtarihi output of the query comes like

    Code:
    1
    2
    4
    4
    5
    So I need to change the query giving the numbers using the islemtarihi,kayitID sections both. So the query will first look at islemtarihi and if the islemtarihi is same, then use kayitID to give the order.

    Can anybody help me with that?

    Thanks
    telmessos

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,020
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    number the rows in your application language as you process the result set returned by the query

    doing it with SQL is far more inefficient and difficult, and you will not be able to "break ties" like you want to

    at least this time you did not use "dummy" table and column names, eh
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Feb 2010
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I am not going to use it for an application. I will export it into a txt file.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,020
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    have a look at the documentation for Using auto_increment --
    For MyISAM tables you can specify AUTO_INCREMENT on a secondary column in a multiple-column index. In this case, the generated value for the AUTO_INCREMENT column is calculated as MAX(auto_increment_column) + 1 WHERE prefix=given-prefix. This is useful when you want to put data into ordered groups.
    all you have to do is define a "holding" table, with the appropriate grouping columns, and then run your query into this table, and let the auto_increment assign the "record number" within each group (islemtarihi)

    then just export the holding table
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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