Count of Data for a specific field

[BeesKnees]

I would like to count a fields that relate to a specific product or product group

How do I number the rows for a specific product and not all the data in the query. 

[micheledenardi]

Your question is not so clear... can you explain better? Thx

[BeesKnees]

I would like script that gives me a sequential number for each product.
Thus for product j8909 I would like the number for the earliest date stamp
record to be one then 2 for the second one, then 3 for the third
Autonumber does not work because the fields in the database wont sort how i
want them to.
Product Number StartDateTime EndDateTime Level Duration
j8909 10:08:44:26 10:08:50:58 96% 00:00:06:32
j8909 10:08:37:35 10:08:44:26 98% 00:00:06:51
j8909 10:07:26:53 10:08:37:35 96% 00:01:10:42
j8909 09:13:51:24 10:07:26:53 100% 00:17:35:29
j8909 09:13:13:29 09:13:51:24 62% 00:00:37:55
j8909 09:07:36:07 09:13:13:29 64% 00:05:37:22
j8910 09:07:28:23 09:07:36:07 64% 00:00:07:44
j8911 09:07:23:14 09:07:28:23 64% 00:00:05:09
j8912 09:04:19:51 09:07:23:14 64% 00:03:03:23
j8913 08:21:31:53 09:04:19:51 75% 00:06:47:58
j8914 08:21:04:40 08:21:31:53 71% 00:00:27:13

[micheledenardi]

Maybe something like this:

Temp:
load *,
if([Product Number]=Peek([Product Number]),peek(Sequential)+1,1) as Sequential
inline [
Product Number,StartDateTime,EndDateTime,Level,Duration
j8909,10:08:44:26,10:08:50:58,96%,00:00:06:32
j8909,10:08:37:35,10:08:44:26,98%,00:00:06:51
j8909,10:07:26:53,10:08:37:35,96%,00:01:10:42
j8909,09:13:51:24,10:07:26:53,100%,00:17:35:29
j8909,09:13:13:29,09:13:51:24,62%,00:00:37:55
j8909,09:07:36:07,09:13:13:29,64%,00:05:37:22
j8910,09:07:28:23,09:07:36:07,64%,00:00:07:44
j8911,09:07:23:14,09:07:28:23,64%,00:00:05:09
j8912,09:04:19:51,09:07:23:14,64%,00:03:03:23
j8913,08:21:31:53,09:04:19:51,75%,00:06:47:58
j8914,08:21:04:40,08:21:31:53,71%,00:00:27:13];

 

[BeesKnees]

This works, but I have to start the sequence at the earliest time stamp to
the latest.

The data that I provided is just a sample.

[micheledenardi · Accepted solution]

Just add an Order By statement:

Temp:
load *
inline [
Product Number,StartDateTime,EndDateTime,Level,Duration
j8909,10:08:44:26,10:08:50:58,96%,00:00:06:32
j8909,10:08:37:35,10:08:44:26,98%,00:00:06:51
j8909,10:07:26:53,10:08:37:35,96%,00:01:10:42
j8909,09:13:51:24,10:07:26:53,100%,00:17:35:29
j8909,09:13:13:29,09:13:51:24,62%,00:00:37:55
j8909,09:07:36:07,09:13:13:29,64%,00:05:37:22
j8910,09:07:28:23,09:07:36:07,64%,00:00:07:44
j8911,09:07:23:14,09:07:28:23,64%,00:00:05:09
j8912,09:04:19:51,09:07:23:14,64%,00:03:03:23
j8913,08:21:31:53,09:04:19:51,75%,00:06:47:58
j8914,08:21:04:40,08:21:31:53,71%,00:00:27:13];


Final:
Load *,
if([Product Number]=Peek([Product Number]),peek(Sequential)+1,1) as Sequential
Resident Temp
order by StartDateTime;
Drop Table Temp;

[BeesKnees]

The data is refusing to sort.

I am not sure what the problem is.