auto numbering

[Hans de Korte]

Hello,

For analyses and reporting I would like to add a 'ranking' to a
table/view.
Example:
Using the 'order by desc' clause in query I get a list of Customers
ordered by Turnover (descending). I would like to add that ranking
numbers (same as recordnumbers) in the query. I would like to have the
following result:
Cust_nr Cust_Name Turnover_2004 Ranking
002234 Bayer 139.000 1
003456 Rentokill 123.456 2
001231 Air France 105.000 3
etc.

When the 'ranking' is part of the query/table I can use this ranking in
an other query.

Important: This questions is not about making an (empty) table structure
for filling in by an application and generating a new unique number each
time a record is added.

I hope you can help me.

Thanks,

Hans

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Hugo Kornelis]

On 10 Mar 2005 03:14:43 -0600, Hans de Korte wrote:

Hello,

For analyses and reporting I would like to add a 'ranking' to a
table/view.
Example:
Using the 'order by desc' clause in query I get a list of Customers
ordered by Turnover (descending). I would like to add that ranking
numbers (same as recordnumbers) in the query. I would like to have the
following result:
Cust_nr Cust_Name Turnover_2004 Ranking
002234 Bayer 139.000 1
003456 Rentokill 123.456 2
001231 Air France 105.000 3
etc.

When the 'ranking' is part of the query/table I can use this ranking in
an other query.

(snip)

Hi Hans,

I'd recommend against storing it in the table, as you'll need to update
all rankings each time some data in the table changes. (Of course, if
you have a static database, where data won't change but that you have to
run extensive reports off, things change).

In a live database, I'd define a view to hold the ranking:

CREATE VIEW CustomersRanked
AS
SELECT Cust_nr, Cust_Name, Turnover_2004,
(SELECT COUNT(*)
FROM Customers AS b
WHERE b.Turnover_2004 > a.Turnover_2004) + 1 AS Ranking
FROM Customers AS a
ORDER BY Turnover_2004 DESC
(untested)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Hans]

Thanks, the soluttion is working excellent!!

Regards,

Hans
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!