Jump to content


Photo

generating serial number in slect query


  • Please log in to reply
1 reply to this topic

#1 shan_cool

shan_cool
  • Members
  • PipPipPip
  • Advanced Member
  • 53 posts
  • LocationChennai,India

Posted 31 January 2006 - 06:30 AM

Hi all,
I need to diplay serial number starting from 1st row.. till end.. i cant use auto increment for that bcz some middle rows might be deleted... i need to maintain the series like

1 field1, filed2,......
2 field1, filed2,......
3 field1, filed2,......
.....


when i write a query as,
SELECT count(*) as serial_no,rcnt,Lpad(defect_id,5,0) Def_Id,proj_name,prod_name,module_name,mail_id,developer_name,def_severity,def_priority,round,def_status,
def_date,caseno,lead_mail_id,exp_out,actual_out,mod_lead_mail_id FROM dt_defect where def_status <> 'Rejected' group by defect_id order by serial_no

it displays as,

1 1 00001 prjname prdname...................
1 2 00002 prjname1 prdname1................
1 3 00003 prjname2 prdname2.................
......................



but i need it as,

1 1 00001 prjname prdname...........
2 2 00002 prjname1 prdname1...........
3 3 00003 prjname2 prdname2...............
..................


rgds,
shan



#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 31 January 2006 - 06:45 AM

No need to start another thread for this. Regardless, if you want to simply "number" the rows that your result set returns, you can simply use a server variable, set it to zero before you run your SELECT query, and increment it with each row using "@cnt := @cnt+1 " in your retrieved column list.

Hope that helps.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users