RowNumber in a query

[Cryothic]

Hi,

is it possible to place an extra field in a query with the rownumbers?
So, if the usual result of the query would be:

Name1 | Street1 | City1
Name2 | Street2 | City2
-----------------------

I would like:

1 | Name1 | Street1 | City1
2 | Name2 | Street2 | City2

I heard there is a ROWNUM function for Oracle, but I use MSAccess.

Thanx

 

[KF]

Go to thread701-245983 Tranman just helped me with this same thing.

KF,

 

[Tranman]

Hi Cryothic,
Saw your post. I know what you mean about rownum in Oracle. I use it quite a bit. You are also correct that there is no corresponding function in Access.

You need to know that what I showed KF works in an append query, but will not work in a select query. If you try to do the same thing in a select, the data will change every time you look at the result set. Not every time you run the query, but every time you *look at* the results. Yep, just scrolling down through the data changes it. Heaven knows why.

If you are wanting to number rows in a report, there is a fairly simple way (which I unfortunately don't know) to do that.

Good luck,
Paul (Tranman)

 

[Cryothic]

Thanx, I looked at it, but this is VBA?

I might be in the wrong forum then, but i'm using Delphi.
I used this forum because I hoped that this problem could be solved using only SQL.

Or am I wrong? Do you use these functions in the SQL-code?

Greetings,
Jeroen (Cryothic) Oostwouder

 

[cmmrfrds]

You can try something like the following but it requires an aggregate function the count(*), so you will need to do group by. Also, a non-equal join. This may work for some applications. Assumes Name2 is unique - should be ID or something.

Select count(*), c1.Name2, c1.Street2, c1.City2
from customer c1, customer c2
where c1.Name2 <= c2.Name2
group by c1.Name2, c1.Street2, c1.City2
order by c1.Name2

 

[Cryothic]

Ok, thanx.

It works