• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

SQL question: returning the posts between rank y and z

 
Ranch Hand
Posts: 904
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Ranch Hand
Posts: 1143
1
Eclipse IDE Oracle Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Ranch Hand
Posts: 221
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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!
 
reply
    Bookmark Topic Watch Topic
  • New Topic