autonumber column in query

[lookforsmt]

Hi! friends
how can I autonumber in query

 

[lookforsmt]

updated a png file as example

 

[CJ_London]

define 'autonumber' - do you mean you want a count of refno's <=to the refno in the current record of the query (i.e. starting from 1)? or something else?

if it is the simple count then something like

SELECT entrydate, refno, (select count(*) from mytable T where refno<=mytable.refno) as auton
FROM mytable
ORDER BY refno

 

[lookforsmt]

Thank you for your response

1) I am still unable to get the sequence nos 1,2,3,....

2) Also, I can know how I can get the sequence nos 1,2,3 for date column once the date changes the sequence nos starts again as 1,2,3,..

 

[CJ_London]

1) I am still unable to get the sequence nos 1,2,3,....

provide the actual sql you are using - saying it doesn't work does not help to resolve the problem

2) Also, I can know how I can get the sequence nos 1,2,3 for date column once the date changes the sequence nos starts again as 1,2,3,..

you should have said this was what you required in the first place - can answer once you

a) provide the sql you are now using that doesn't work

b) clarify what you mean by autonumber (since these do not repeat, but you second post implies they are required to repeat)

if necessary, provide an example of what you expect to see with an autonumber

 

[lookforsmt]

Thank you again, I think I was not clear in the first place but I found the query I was looking in this forum. " RowNumber"
I will close this post now.

 

[lookforsmt]

thanks for your response

below is the query I figured out

Num: (SELECT Count(Id) + 1 FROM Update1 AS B WHERE B.EntryDate = Update1.EntryDate AND B.Id < update1.Id)

regrds

 

[CJ_London]

yes that will work, alternative is

Num: (SELECT Count(*) FROM Update1 AS B WHERE B.EntryDate = Update1.EntryDate AND B.Id <= update1.Id)

for it to make sense when displayed, you will need to order your main query by EntryDate and PK