creating a row number within an Acess SQL query

bl*****@carolina.rr.com wrote in message
<11**********************@j33g2000cwa.googlegroups .com> :

I've seen this asked several times, but never a straight answer.
I have a query returning a set of sorted rows. Let's say I get back:

Alice
Bob
Charlie

I want to add a number ranking, so it becomes:

1 | Alice
2 | Bob
3 | Charlie

Is there some way to place a variable within the SQL statement that
increments by 1 for each row?

Something like this?

select
(select count(s.name)
from mytable s
where s.name < = t.name) as mycount,
t.name
from mytable t
order by t.name

This will probably be a drain on recourses (if it works), if there are
lot of rows, and the field to sort on/set criteria on, needs to be
unique.

--
Roy-Vidar

It's a small number of rows coming in the query, and uniqueness doesn't
need to be enforced in this instance.
Your query ALMOST worked. It did give a number, but not in the right
order or at the right time. Explanation:

If my original data set I queried was set up like:
Charlie
Alice
Bob

Then your query returned this:
2 | Alice
3 | Bob
1 | Charlie

It used the original position, not the position after sorting.
I can try passing my query results through another query that does
nothing but add your number to it, but I'd like something a little
cleaner, a "one shot" kind of a deal.

bl*****@carolina.rr.com wrote:

I've seen this asked several times, but never a straight answer.
I have a query returning a set of sorted rows. Let's say I get back:

Alice
Bob
Charlie

I want to add a number ranking, so it becomes:

1 | Alice
2 | Bob
3 | Charlie

Is there some way to place a variable within the SQL statement that
increments by 1 for each row?

Try this thread:

http://groups.google.com/group/comp....b73e9bfb888698

James A. Fortune
CD********@FortuneJames.com

bl*****@carolina.rr.com wrote in message
<11*********************@g10g2000cwb.googlegroups. com> :

It's a small number of rows coming in the query, and uniqueness doesn't
need to be enforced in this instance.
Your query ALMOST worked. It did give a number, but not in the right
order or at the right time. Explanation:

If my original data set I queried was set up like:
Charlie
Alice
Bob

Then your query returned this:
2 | Alice
3 | Bob
1 | Charlie

It used the original position, not the position after sorting.
I can try passing my query results through another query that does
nothing but add your number to it, but I'd like something a little
cleaner, a "one shot" kind of a deal.

Strange ...

When I stuffed a table only containing one field (name) with Alice,
Bob,
and Charlie, then run the excact query I gave you, it resulted in
exactly what you asked for in the initial post.

As you can see by the SQL, for this to work, the field in question will
need to be
1 - unique
2 - be the field on which you sort
3 - be the field used in the comparision in the subquery

For more info, try searching on "ranking query", here are some links
(watch for linebreaks)

http://support.microsoft.com/?kbid=208946
http://msdn.microsoft.com/library/de...tml/sa01j1.asp

--
Roy-Vidar

The normal way is to add an AutoNumber field to your table. Then Access
will create a sequential number for you.
But if you you run a query on that field, you might get
12 Bob
13 Charlie
14 Alice
So the auto-number depends on the order in which you created the records.
On Wed, 29 Mar 2006 20:40:53 +0200, <bl*****@carolina.rr.com> wrote:

I've seen this asked several times, but never a straight answer.
I have a query returning a set of sorted rows. Let's say I get back:

Alice
Bob
Charlie

I want to add a number ranking, so it becomes:

1 | Alice
2 | Bob
3 | Charlie

Is there some way to place a variable within the SQL statement that
increments by 1 for each row?

--
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/

Probably because without any SORT BY clause in the query, Access will
natively sort by the table's primary key, which in the OP's post I'd
guess is an autonumber field.

Just a guess.

Much warmth,

planetthoughtful

---
"Lost in thought"
http://www.planetthoughtful.org

All things are possbile. This is.
Some things are unnecessary. This is.
Some things are foolish. This is.

planetthoughtful wrote in message
<11*********************@t31g2000cwb.googlegroups. com> :

Probably because without any SORT BY clause in the query, Access will
natively sort by the table's primary key, which in the OP's post I'd
guess is an autonumber field.

As I've understood, an ORDER BY clause, is not necessary for an
ordinary
ranking query to work, I like to add it though, to present the result
in
the same order as the ranking, but again, I don't think it is entirely
necessary. If you look again, you should be able to find an ORDER BY
clause as the last clause of my suggested SQL statement.

Using Count(*), will usually also do, in stead of Count(FieldName), I
don't know why I keep using the latter version.

But I do think you need a sortable and unique *field*, on which to set
the criterion in the subquery. If the field isn't unique, equal
values/duplicates will get equal rank.

For more samples/explanations, check out for instance
http://support.microsoft.com/kb/q182568/
http://www.fabalou.com/Access/Querie...TotalQuery.asp
http://ourworld.compuserve.com/homep...ip.htm#AUTONUM

If I've competely misunderstood the concept of "Ranking queries" or
"Running totals", I'm happy to learn new ways (except usage of Domain
Aggregates, that is).

--
Roy-Vidar

Lyle Fairfield wrote:

All things are possbile. This is.
Some things are unnecessary. This is.
Some things are foolish. This is.

This reminds me of the time an itinerant street preacher came to
Oakland U.

That preacher called Becky a whore. That wasn't nice. Of course,
everybody on campus knows he was right; but it still wasn't nice. --
Ken Harrington

Putting a row number on an Access query does fly 180 degrees opposite
that flown by database theory. Maybe this will be the simplest way to
solve some future problem. Maybe not. The future's not ours to see
:-).

James A. Fortune
CD********@FortuneJames.com