Jump to content

Archived

This topic is now archived and is closed to further replies.

shan_cool

generating serial number in slect query

Recommended Posts

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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.