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.

 
Go Back  dBforums > Database Server Software > MySQL > Database Ordering / Selecting a Range

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 10-28-08, 09:42
Atari Atari is offline
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
Reply With Quote
  #2 (permalink)  
Old 10-28-08, 10:31
gvee gvee is offline
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.
__________________
George
Twitter | Blog
Reply With Quote
  #3 (permalink)  
Old 10-28-08, 10:32
mike_bike_kite mike_bike_kite is offline
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
Reply With Quote
  #4 (permalink)  
Old 10-28-08, 10:48
Atari Atari is offline
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
Reply With Quote
  #5 (permalink)  
Old 10-28-08, 11:03
mike_bike_kite mike_bike_kite is offline
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
Reply With Quote
  #6 (permalink)  
Old 10-28-08, 13:18
gvee gvee is offline
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
__________________
George
Twitter | Blog
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On