[RESOLVED] serial number in recordset when such field not available

[Radhesham]

Hi all
I have a table customers with fields
custcode
custname
city


SELECT srno, custname FROM customers ORDER BY custname

In this query, srno should increament automatically.
Is it possible?

I want to fire a query which will give me following result.
1 customer1
2 customer2
etc

Thanks

[LinXG]

In this query, srno should increament automatically. Is it possible?

No! srno is not a field in your table so it cannot be selected by your query.

Add a new field to your table called srno and set it to auto increment, then your query will work.

[ganeshmoorthy]

what is the database that you are using...try ROW_NUMBER()

[Radhesham]

Thanks for reply.

GaneshMoorthy:
Data base is MS SQL Server.
How to use row_number is select query. will you pls give an example.
Will row_number work properly with ms sql server database table?

LinXg:
Autoincreament field may not be possible in this case as query is intended for data ordered by custname field and where data insert might have happened in any order and so autoincreament field may not have serial values.

[ganeshmoorthy]

Radhesham, I found this example to get a rownumber in your query...

SELECT CustCode, CustName, 
(SELECT COUNT(*) FROM Customers C2 WHERE C2.CustCode >= C.CustCode) AS SrNo
FROM Customers C
ORDER BY CustCode

hope this works for you...

[Radhesham]

Thanks.
Thank you for providing exact solution.
Radhesham

[ganeshmoorthy]

Radhesham: You are most welcome !