470,815 Members | 3,163 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,815 developers. It's quick & easy.

auto numbering

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!
Jul 23 '05 #1
2 1275
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)
Jul 23 '05 #2

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!
Jul 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by Charles McCaffery | last post: by
6 posts views Thread by MJ | last post: by
3 posts views Thread by Chris | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.