record number in a query

  • Thread starter Thread starter Finn Jensen
  • Start date Start date
F

Finn Jensen

I need to add a field to a select statement.

SELECT *, recnumber FROM MyTable WHERE CategoryID=3

recnumber should start with 1 and be increased by 1 for each record
found by the select statement.

How do I do that?

TIA
Finn
 
Considering that your query has no ORDER BY clause, the order of records
selected by your query can vary each time you run it (as data are
added/edited in the table).

Let's examine the purpose for the renumber field value...what do you want to
do with it? There may be a better approach to accomplishing that result.
 
I have a table: Pictures
picid: autonum
personname: text
picfile: text
category: integer

Pictures is often deleted and new is added.

These pictures is listed as icons on an asp page by category ordered
by personname.
On click on one of these icons I want to show an other asp page that
shows one picture at a time starting with the picture selected by
clicking the icon. There must be a Next and Prev link that links to
the next and previus picture. Both lists is ordered by personname (the
person that made the picture)

select *, tmpAutonum from pictures ordered by personname
 
OK - to do what you seek, you could try an SQL statement similar to the
following:

SELECT Pictures.*, (SELECT Count(*) FROM Pictures AS T WHERE T.picid<=
Pictures .picid) AS RecNumber
FROM Pictures;
 
I see I left a space inadvertently in the posted SQL:

SELECT Pictures.*, (SELECT Count(*) FROM Pictures AS T WHERE T.picid<=
Pictures.picid) AS RecNumber
FROM Pictures;
 
Hi Ken,

I have a similar need to list record number in a query. I tried your SQL text, and it works if I wanted ALL of the table records in the query results. However, I have one additional piece of criteria: If YesNoField = "No". How can I incorporate this? If my query results in, say, 10 records, I need the RecNumber field to display the actual position in the table, not 1 thru 10.

Thanks for any help you can provide.
 
Back
Top