Sequential Numbering resents on a group

[HollyylloH]

I need a column to be numbered 1,2,3,4,etc for each item in a group(JobNo),
for
example:

OverallID JobNo MYID
1 2000 1
2 2000 2
3 2000 3
4 2000 4
5 2001 1
6 2001 2
7 2002 1
8 2002 2

etc

I have the following code which I think is close to working:

SELECT b.OverallID, b.JobNo, (SELECT COUNT(*) FROM TableName As b WHERE
b.JobNo=JobNo AND b.Overall <= Overall) AS MYID
FROM TableName AS b
ORDER BY b.JobNo;

However, instead of returning the numbering I want, It is counting the total
number of records (1981). So, the entry is 1981 for every row in column MYID.

What is wrong with my code?

 

[Marshall Barton]

I need a column to be numbered 1,2,3,4,etc for each item in a group(JobNo),
for
example:

OverallID JobNo MYID
1 2000 1
2 2000 2
3 2000 3
4 2000 4
5 2001 1
6 2001 2
7 2002 1
8 2002 2

etc

I have the following code which I think is close to working:

SELECT b.OverallID, b.JobNo, (SELECT COUNT(*) FROM TableName As b WHERE
b.JobNo=JobNo AND b.Overall <= Overall) AS MYID
FROM TableName AS b
ORDER BY b.JobNo;

However, instead of returning the numbering I want, It is counting the total
number of records (1981). So, the entry is 1981 for every row in column MYID.

The query seems to have the correct logic, but you have the
same alias (b) on both instances of TableName. I'm suprised
that the query designer even allows that.

Try changing the aliases to something more like:

SELECT b.OverallID, b.JobNo,
(SELECT COUNT(*)
FROM TableName As T
WHERE T.JobNo = b.JobNo AND T.Overall <= b.Overall
) AS MYID
FROM TableName AS b
ORDER BY b.JobNo;

 

[HollyylloH]

Thank you, that did it.

The query seems to have the correct logic, but you have the
same alias (b) on both instances of TableName. I'm suprised
that the query designer even allows that.

Try changing the aliases to something more like:

SELECT b.OverallID, b.JobNo,
(SELECT COUNT(*)
FROM TableName As T
WHERE T.JobNo = b.JobNo AND T.Overall <= b.Overall
) AS MYID
FROM TableName AS b
ORDER BY b.JobNo;