Full Version: Numbering Rows in a Query
UtterAccess Discussion Forums > Microsoft® Access > Access Queries
ktrnhbr
I am looking for help numbering the rows of a query sequentially. I found some code posted on an old thread (in spanish) but I could not get it to work. Any assistance would be greatly appreciated.
Thank you.
mishej
Do you have a field that determines the sort order? A date or serial number or perhaps text that needs to be sorted alphabetically?
If you have something like that you can use a counting query. The sql looks like this:
CODE
SELECT p.KeyPct, (SELECT COUNT(*) FROM tblMiscPct WHERE KeyPct >= p.KeyPct) AS Rank
FROM tblMiscPct AS p
Ordere a field that determines the sort order? A date or serial number or perhaps text that needs to be sorted alphabetically?
If you have something like that you can use a counting query. The sql looks like this:
CODE
SELECT p.KeyPct, (SELECT COUNT(*) FROM tblMiscPct WHERE KeyPct >= p.KeyPct) AS Rank
FROM tblMiscPct AS p
ORDER BY p.KeyPct DESC;

THere we rank a set of records by a percent field. By counting records with an equal or greater value than the current record we come up with a "ranking" or "row" number.
There can be ties, so the ORDER BY becomes more important.
If you post more info on your needs someone will provide more exact help.
ktrnhbr
I am sorting the records by a class field (classes are a - zz) where there will be more than one entry in each class and I want to get a numbered list without any duplicate numbers. Will the code you provided create duplicate numbers (rankings) under these conditions?
mishej
Yes if you use just the class field. If there are more than one record for a class how do you determine which one is numbered first sequentially? Must be another field you use to "break the tie". Otherwise, how do you order them?
ktrnhbr
Yes, after class I use an assigned number (PlateNum) to break the tie. Pardon my ignorance, but I can not seem to get this to work. I get the same number for each record.
mishej
Based on what you've shared the SQL might look like this:
CODE
SELECT
  t.class
, t.PlateNum
, (SELECT COUNT(*) FROM someTableYouAreUsing WHERE class >= t.class and PlateNum >= t.PlateNum) AS Rank
FROM
  someTableYouAreUsing AS t
Orderhat you've shared the SQL might look like this:
CODE
SELECT
  t.class
, t.PlateNum
, (SELECT COUNT(*) FROM someTableYouAreUsing WHERE class >= t.class and PlateNum >= t.PlateNum) AS Rank
FROM
  someTableYouAreUsing AS t
ORDER BY
  t.class, t.PlateNum;
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.