| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |

10-28-08, 09:42
|
|
Registered User
|
|
Join Date: Nov 2004
Posts: 35
|
|
|
Database Ordering / Selecting a Range
|
|
Hi guys,
Just a quick question. Consider an ordered list, where non-sequential number values are attributed to each row in the table.
name varchar, score int
john, 10
julie, 45
mike, 1
han, 46
luke, 100
What I need to select, is:
- given a username, find the people that have two scores above, and two scores below
This is a massive table, and programmatically churning through millions of records is not an option at this time.
I realize that ideally, this calculation would be done at insertion time...but given the above, is there a reasonable query possible?
Thanks.
A
|
|

10-28-08, 10:31
|
|
www.gvee.co.uk
|
|
Join Date: Jan 2007
Location: UK
Posts: 10,156
|
|
What would you want to return if we expanded your data as follows:
john, 10
mark, 10
frank, 10
jim, 10
pete, 11
julie, 45
mike, 1
han, 46
luke, 100
The given username is Pete.
|
|

10-28-08, 10:32
|
|
vaguely human
|
|
Join Date: Jun 2007
Location: London
Posts: 2,519
|
|
|
|
The following works on a small test table. You need to put the value (julie in this case) into both parts.
Assuming an index on score you could try artificially limiting the amount of records being returned by limiting the records returned to be those within +/- 1000 of the original user's score. This might make it quicker to scan through.
Code:
/*
create table my_tab ( name varchar(20), score int );
insert my_tab values ( 'john', 10 );
insert my_tab values ( 'julie', 45 );
insert my_tab values ( 'mike', 1 );
insert my_tab values ( 'han', 46 );
insert my_tab values ( 'luke', 100 );
*/
select name, score
from (
select t2.name, t2.score
from my_tab t1,
my_tab t2
where t1.name = 'julie'
and t2.score < t1.score
order by t2.score desc
limit 2
) xxx
union
select name, score
from (
select t2.name, t2.score
from my_tab t1,
my_tab t2
where t1.name = 'julie'
and t2.score > t1.score
order by t2.score desc
limit 2
) yyy
Mike
|
|

10-28-08, 10:48
|
|
Registered User
|
|
Join Date: Nov 2004
Posts: 35
|
|
Hi mike,
The issue is that no range is guaranteed.
Similar records could be considered of irrelevant order, so any shuffle along those lines would be fine.
Searching for Pete in the previously posited example could return:
Frank
Jim
Pete <-- range anchor
Julie
Han
|
|

10-28-08, 11:03
|
|
vaguely human
|
|
Join Date: Jun 2007
Location: London
Posts: 2,519
|
|
How would the database know that it should show frank and jim rather than john and mark who also have a score of 10? George's point was that there is no inherent ordering in a database table. Is there another field that can be used? My code above would just pick any 2 of the records with a score of 10.
Another small point is that you initially asked for the two records above and the two records below but not the main record. To achieve this (using my sql) just change the '<' to '<='
Does the code work on your large table - I didn't have time to create a million records to test it against
Mike
|
|

10-28-08, 13:18
|
|
www.gvee.co.uk
|
|
Join Date: Jan 2007
Location: UK
Posts: 10,156
|
|
Quote:
|
Originally Posted by mike_bike_kite
George's point was that there is no inherent ordering in a database table
|
Bang on
You'll need to store something like the date_created against each record for this to work correctly, because the use of LIMIT without an ORDER BY clause, is meaningless
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|