By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,946 Members | 697 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,946 IT Pros & Developers. It's quick & easy.

How do I rank numbers by using Sql

P: n/a
I have a table like this:

Numbers
10
20
30
40

How can I make a sql in Access? I want it like this:

Numbers Rank
10 4
20 3
30 2
40 1

Aug 24 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Type something like this into the Field row in query design:
Rank: (SELECT Count(*)
FROM Table1 AS Dupe
WHERE Dupe.Numbers <= Table1.Numbers)

It's one line.
Substitute your table name for Table1.
Results will be read-only.

Alternative that is slower but not read-only:
DCount("*", "Table1", "Numbers <= " & [Numbers])

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Helge's" <he*************@chello.nowrote in message
news:11*********************@75g2000cwc.googlegrou ps.com...
>I have a table like this:

Numbers
10
20
30
40

How can I make a sql in Access? I want it like this:

Numbers Rank
10 4
20 3
30 2
40 1

Aug 24 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.