numbering rows

  • Thread starter Thread starter Florin
  • Start date Start date
F

Florin

I have a table :

Nrcrt Field 2 Field 3
F 40
F 40
F 50
F 50
F 50
and I want fill field "Nrcrt" (numbering rows depending on Field3) :

Nrcrt Field 2 Field 3
1 F 40
2 F 40
1 F 50
2 F 50
3 F 50

How could I do that?
Thanks
 
Use a Ranking in a group query like this ---
SELECT Q.[Group], Q.[Item_no], Q.[Points], (SELECT COUNT(*) FROM [Product] Q1
WHERE Q1.[Group] = Q.[Group]
AND Q1.[Points] < Q.[Points])+1 AS Rank
FROM Product AS Q
ORDER BY Q.[Group], Q.[Points] DESC;
 
Karl, can you give me more details?
What is Q and Q1? Q is alias for table Product?

Thanks


karl dewey said:
Use a Ranking in a group query like this ---
SELECT Q.[Group], Q.[Item_no], Q.[Points], (SELECT COUNT(*) FROM [Product]
Q1
WHERE Q1.[Group] = Q.[Group]
AND Q1.[Points] < Q.[Points])+1 AS Rank
FROM Product AS Q
ORDER BY Q.[Group], Q.[Points] DESC;

--
KARL DEWEY
Build a little - Test a little


Florin said:
I have a table :

Nrcrt Field 2 Field 3
F 40
F 40
F 50
F 50
F 50
and I want fill field "Nrcrt" (numbering rows depending on Field3) :

Nrcrt Field 2 Field 3
1 F 40
2 F 40
1 F 50
2 F 50
3 F 50

How could I do that?
Thanks
Click to expand...
Click to expand...
 
Yes, Q and Q1 are aliases for table Product.

You will need to include all fields that form a group like this --
Q1.[Group] = Q.[Group]
AND Q1.[XX] = Q.[YY]
AND Q1.[ZZ] = Q.[ZZ]
AND


Florin said:
Karl, can you give me more details?
What is Q and Q1? Q is alias for table Product?

Thanks


karl dewey said:
Use a Ranking in a group query like this ---
SELECT Q.[Group], Q.[Item_no], Q.[Points], (SELECT COUNT(*) FROM [Product]
Q1
WHERE Q1.[Group] = Q.[Group]
AND Q1.[Points] < Q.[Points])+1 AS Rank
FROM Product AS Q
ORDER BY Q.[Group], Q.[Points] DESC;

--
KARL DEWEY
Build a little - Test a little


Florin said:
I have a table :

Nrcrt Field 2 Field 3
F 40
F 40
F 50
F 50
F 50
and I want fill field "Nrcrt" (numbering rows depending on Field3) :

Nrcrt Field 2 Field 3
1 F 40
2 F 40
1 F 50
2 F 50
3 F 50

How could I do that?
Thanks
Click to expand...
Click to expand...
Click to expand...
 
Back
Top