Add autonumber into query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there a way to use an autonumber in a query? Not a pre-existing field. I'm
running this

SELECT TOP 50 Count(HOME.EMPLOYER) AS CCount, HOME.EMPLOYER
FROM HOME
GROUP BY HOME.EMPLOYER
ORDER BY Count(HOME.EMPLOYER) DESC;


I want an integer to the left to show the actual rank. I know with ADO I would
be incrementing anyway with i, but how do I do it with a query
(e-mail address removed)
 
Dear Spam:

You used the correct terminology when you called this a "rank."

This can be accomplished with a "correlated subquery."

SELECT TOP 50 EMPLOYER,
(SELECT COUNT(*) FROM HOME T2
WHERE T2.EMPLOYER < T1.EMPLOYER) + 1 AS Rank
FROM HOME T1
GROUP BY EMPLOYER
ORDER BY EMPLOYER DESC;

If EMPLOYER is not unique, the rows that are tied will be ranked the
same. You must have a unique sort order in order to get a unique
ranking.

P. S.: Do you have a name? I think I've responded to a few posts
you've made, so could you spare just a first name for us?

Is there a way to use an autonumber in a query? Not a pre-existing field. I'm
running this

SELECT TOP 50 Count(HOME.EMPLOYER) AS CCount, HOME.EMPLOYER
FROM HOME
GROUP BY HOME.EMPLOYER
ORDER BY Count(HOME.EMPLOYER) DESC;


I want an integer to the left to show the actual rank. I know with ADO I would
be incrementing anyway with i, but how do I do it with a query
(e-mail address removed)
Click to expand...

Tom Ellison
Ellison Enterprises - Your One Stop IT Experts
 
I'm not sure I understand how the query works. I serialize a reserved word? Is
it like some kind of Rank function?
(e-mail address removed)
 
Dear Spam:

You used the correct terminology when you called this a "rank."

This can be accomplished with a "correlated subquery."

SELECT TOP 50 EMPLOYER,
(SELECT COUNT(*) FROM HOME T2
WHERE T2.EMPLOYER < T1.EMPLOYER) + 1 AS Rank
FROM HOME T1
GROUP BY EMPLOYER
ORDER BY EMPLOYER DESC;

If EMPLOYER is not unique, the rows that are tied will be ranked the
same. You must have a unique sort order in order to get a unique
ranking.

P. S.: Do you have a name? I think I've responded to a few posts
you've made, so could you spare just a first name for us?

Jim

http://www.querytools.net/

http://www.angelfire.com/nj4/cybervision/Tools.html

the adds at the bottom of the second link aren't mine.
(e-mail address removed)
 
Back
Top