Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2004
    Posts
    35

    Unanswered: 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

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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
    Home | Blog

  3. #3
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    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

  4. #4
    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

  5. #5
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    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

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •