row count in query

[KT]

Would like to know how to add a dynamic counter to a
query. I have a query that is grouping on several fields;
I would like my output to contain a row number for each
record. Any help much appreciated.

 

[Tom Ellison]

Dear kmak:

The concept of a "row number" makes no sense unless you first impose an
order on the rows returned by your query. If the query is ordered then it
is likely you want the numbering to follow this same order.

Such a feature can be accomplished using a correlated subquery as a column,
which I'd typically name "Rank" as it will rank the rows in the results.

If the query is ordered on a single column I'll call RankOrder then this
subquery would look something like:

(SELECT COUNT(*) FROM YourTable T1 WHERE T1.RankOrder < T.RankOrder) AS
Rank

This will assign values starting with 0 for the first row. You may want to
add 1.

If the column RankOrder is not unique, there will be ties in the Rank
values. You may want to add other columns to the ordering till it is
unique. These must also be added to the subquery.

For a more detailed example, you need to first post more details of your
situation. Probably it would be enough to post the SQL you have so far,
omitting the Rank column, but including an ORDER BY clause that shows how
they are to be numbered.

 

[Tom Ellison]

Dear kmak:

I used the term RankOrder to refer to a single column of your table (which
you did not specify). I am assuming there is some column in the table by
which you intend to order the rows of the table in order to make the concept
of a "row number" for every row feasible. Without some ordering of the
table, no such concept as "row number" is sensible.

If you have such a column, substitute its name for "RankOrder" in my sample.
If it requires more than one column to properly order the rows in your
table, then the query will become more complex. If you cannot order the
rows by some combination of columns, the query is impossible.

I had to invent such columns because your original post contains few details
of what you're doing. If you will give such specifics, I can provide more
specific solutions. I suggest you could post the query to which you want to
"add a dynamic counter". From that, I can probably be quite specific. Be
sure to add sorting to this query so it puts the results into the order you
want them counted.