Adding a count column to an SQL statement

[george via AccessMonster.com]

I have an SQL statement that retrieves all of the items in the items table.
When i display the data I want to have a number before each of the records
so that it looks as follows:
1 xxx yyy zzz
2 xxx yyy zzz
3 xxx yyy zzz

etc.

Is there a way in SQL to add the first column? A way to add sequential
numbering in?

Thanks!
~george

 

[Vadim Rapp]

Hello george:
You wrote in conference microsoft.public.access.adp.sqlserver on Thu, 17
Feb 2005 20:13:05 GMT:

gvA> I have an SQL statement that retrieves all of the items in the items
gvA> table. When i display the data I want to have a number before each of
gvA> the records so that it looks as follows:
gvA> 1 xxx yyy zzz
gvA> 2 xxx yyy zzz
gvA> 3 xxx yyy zzz

If this is in report, there's property "running sum" of the textbox, which
is what you need: bind it to the returned 1, and its running sum will be
sequential numbering. If not report, then you can modify your query like this:
if the original query is

select x from table1 order by x

modify it to

SELECT
count(t2.x), t1.x
from table1 t1,table1 t2
where t2.x<=t1.x
group by t1.x
order by t1.x



Vadim