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.

Full Version: Numbering Rows in a Query

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.

Thank you.

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:

Here 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.

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;

FROM tblMiscPct AS p

ORDER BY p.KeyPct DESC;

Here 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.

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?

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?

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.

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

ORDER BY

t.class, t.PlateNum;

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.