create row numbers in query per subgroup

[yvokeijlewer]

I would like to create row numbers in a query per subgroup.
For example:

product client newcolumn
tea 1100 1
bread 1100 2
bacon 1100 3
ice 1100 4
tea 1101 1
bread 1101 2
tea 1102 1
bacon 1102 2
car 1102 3

In other words I would like the query to count the numbers of different
products per client and then number them... I have managed to add
numbering to the query, but I am not able to restart the numbering when
the client changes ....

Hope someone can help me!

Thanks
Yvo

 

[Marshall Barton]

I would like to create row numbers in a query per subgroup.
For example:

product client newcolumn
tea 1100 1
bread 1100 2
bacon 1100 3
ice 1100 4
tea 1101 1
bread 1101 2
tea 1102 1
bacon 1102 2
car 1102 3

In other words I would like the query to count the numbers of different
products per client and then number them... I have managed to add
numbering to the query, but I am not able to restart the numbering when
the client changes ....

I have my doubts about the usefulness of this new column,
but to do it, you **must** have a field (or set of fields)
that can be used to uniquely sort the records. Based on
your example data, I assume it doesn't really matter which
record gets which value in the new column as long as the
values are sequential by client.

Let's say the sorting is ny client and then by product. In
this case, the query qould be somethig like:


SELECT T.product, T.client,
(SELECT Count(*)
FROM yourtable As X
WHERE X.client = T.client
AND X.product <= T.product
) As newcolumn
FROM yourtable As T