Add sequential numbers to unique values

[ellbrandon]

If I have a table that contains 2 columns

ID Serial
1 1234ABCD
2 1234ABCD
3 56789AAA
4 56789AAA
5 56789AAA
6 1011BBBB
7 4321XXXX
8 4321XXXX
9 4321XXXX
10 4321XXXX

How would I make a query that sequentially counts the unique serial numbers? Like this.

ID Serial Sequence_No
1 1234ABCD 1
2 1234ABCD 2
3 56789AAA 1
4 56789AAA 2
5 56789AAA 3
6 1011BBBB 1
7 4321XXXX 1
8 4321XXXX 2
9 4321XXXX 3
10 4321XXXX 4

Thanks

[twinnyfo]

I think it is as simple as:

DCount("ID", "tblYourTable", "Serial = '" & [Serial] & "' AND ID <= " & [ID]) AS Sequence_No

Hope this hepps!

[NeoPa]

Unfortunately the question doesn't match the example so using DCount() would not work - which is another good reason for not using Domain Aggregate functions in a query ;-)

To serialise the records is another matter - and not too straightforward. That's because databases and tables are built on Set Theory and aren't simply Excel spreadsheets.

To achieve this you'd need a sub-query to group the items you intend to serialise and provide the minimum value of the [ID]. This you'd link to the main table in your query using an INNER JOIN. For each resultant record you'd use the [ID] - Min([ID]) from the matching grouped query.

If you want the serialisation to start at one instead of zero then add an extra one.

[twinnyfo]

I do have a question regarding these differing solutions, because I have always had questions about the real difference betwixt using a DCount() Domain Aggregate function and using the Count() function from within SQL. What is the "real" difference, and why does it matter? And, the Domain Aggregate functions are decried as the bane of humanity. I just want to understand a bit better--as I am sure you can probably assist.

Granted, I can see that using a Domain Aggregate function with very large datasets would be slow--but I also see that using Count() with large datasets would create similar delays.

[twinnyfo]

NeoPa,

Yes, although using my method does, in fact, work and produce the results desired, it is probably not the best way. Here is the corrected version:

SELECT ID, 
       Serial 
       (SELECT Count(*) 
        FROM tblYourTable 
        WHERE Serial = T2.Serial 
        AND ID <= T2.ID) AS Sequence_No 
FROM tbYourTable as T2;

I would critique your method in the following way: I can see that your method would work only in situations in which the Serial Numbers are all sequential IDs (granted, this is the dataset provided by the OP). If those Serial Numbers were randomly scattered throughout the table, your method would only find how many records separated each occurrence.

[NeoPa]

Hi Twinny.

You're correct in your critique. I did assume that the example given was a more reliable indicator of the requirement than the question as worded. I felt that had already been indicated. As for what may be an extra requirement of handling randomly sequenced records, I felt allowing the OP to realise that for themselves and being encouraged actually to express it would help them to understand the complexities of the situation a little better. There's no guarantee they would either have perceived the issue or expressed it mind.

NB. Your solution seems the perfect answer. If it hadn't already been selected as Best Answer then I'd have done it myself. I've taken the liberty of splitting the post though, as the extra question, while perfectly fine in itself, isn't what someone is looking for when they search for this particular question.

The evils of Domain Aggregate functions.

Very good question. Bear in mind that each call is a separately set up processing of a dataset. It also has procedural overhead of course, but I expect that to be really minimal. What you should be noticing here is that it's separate from the original SQL of your query, as well as being each separate from the other. One of the great things that an RDBMS engine provides you with is the optimisation of handling data. Particularly with large amounts.

Even if the original SQL has no overlapping data itself (with the Domain Aggregate function data), it's often the case that multiple DA functions within some SQL, process either the same data or just data from the same source. This is more efficiently handled by a single pass through the source data rather than multiple passes through that same data. The cost of setting up a recordset is included in every DA function call and that cost is (relatively) considerable.

Often the cost of such a design is negligible. The understanding of why one would do it the proper way however, is priceless. Good habits mean you fall over fewer such issues in the first place.

I hope that helps.

[twinnyfo]

NeoPa,

Yes, this was very heppful. I think, because of bad practices and inclinations of the past (as well as not fully understanding things) I naturally gravitate toward the Domain Aggregate functions. However, I have begun to incorporate subqueries in my projects more regularly, mostly because, thanks to this site, I’ve gained a better understanding of them, as well as an appreciation for them.

Thanks again for clarifying something for me that helps me understand my craft!

[NeoPa]

Always a pleasure my friend.

Bear in mind you ask more advanced questions than most. This gives an opportunity to share some of the more advanced practices and understanding so that all have access to it. Win-win!