• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

SQL question: returning the posts between rank y and z

 
Svend Rost
Ranch Hand
Posts: 904
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

I have a table containing "football players", each having a score (ie.
their worth). I'd like to be able to return the posts between rank z and y.
For instance the 10 best players (ie. z=1, y=10) or the players between
rank 100 and 200 (ie. z=100, y=200).

My table looks like the following: Player(name, age, score). Let's assume
it's not possible to add a rank field, because the players get their score
update every time they play.

Any ideas?

SELECT * FROM (S * F player ORDER BY score) WHERE ???

It would be neat, if you could add a field in the subquery called rank, so
you could say S * F (ordered list) W rank>z AND rank<y

Thanks in advance,

Svend Rost
 
Avi Abrami
Ranch Hand
Posts: 1141
1
Java Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Svend,
If you are using an Oracle database, then it has this capability built-in in the form of analytic functions.

Just do an internet search for "analytic functions" and you will find lots of information.

To get you going, the below URL is the result of my search:

http://tinyurl.com/8mbnz

Good Luck,
Avi.

P.S. If you're not using Oracle, I guess the above is a waste of your time. Maybe next time you should mention what database you are using.
 
Svend Rost
Ranch Hand
Posts: 904
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Avi Abrami:

P.S. If you're not using Oracle, I guess the above is a waste of your time. Maybe next time you should mention what database you are using.


Thanks for replying Avi. Im using an access database, and what im looking
for is an SQL query.

I've been thinking about something like



But it also seems abit ineffective, if I'd like the posts between rank
10000 and 10200.

Any SQL guru's out there

/Svend Rost
 
Horatio Westock
Ranch Hand
Posts: 221
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi!

There are vendor extensions for this, but unfortunately there isn't much consistency.

In Postgres (and MySQL IIRC) for example:


Would get you the top 10 from the resulting rows. In addition, you can add a second parameter to say which row to start with (offset):



In Access I believe you can do the following (note that I've never tried it):



However, getting a range is a little more complicated. One possibility is that you get, say, the top 100, then get the last 10 of those, in order to get results 90-100.



Ugly!

There are probably other approaches out there. Try searching for 'paging results' or similar, as it's essentially the same problem.

Hope this helps!
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic