How to write the record number on a separate field

Databases
1

Hi all,

I have the table structure of


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).


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


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

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 :wink:

3

I am not going to use it for an application. I will export it into a txt file.

4

have a look at the documentation for Using auto_increment –

[indent]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. [/indent]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