Professional Web Applications Themes

Select a range around a specific row - MySQL

Hi, I need to select a range of ten rows around a specific row such that the specific row floats to the beginning or end of the set as the set approaches the beginning or end of the (ordered) table. So, for example, if I had a hundred rows with the values 1-100 in them, in no particular order, and the specific row was 2, I would need the set 1 2 3 4 5 6 7 8 9 10. If the specific row was 20, I would need something more like 17 18 19 20 21 22 23 24 ...

  1. #1

    Default Select a range around a specific row

    Hi,

    I need to select a range of ten rows around a specific row such that the
    specific row floats to the beginning or end of the set as the set
    approaches the beginning or end of the (ordered) table.

    So, for example, if I had a hundred rows with the values 1-100 in them,
    in no particular order, and the specific row was 2, I would need the set
    1 2 3 4 5 6 7 8 9 10. If the specific row was 20, I would need
    something more like 17 18 19 20 21 22 23 24 25 26, and if the row was
    100, I would need 91 92 93 94 95 96 97 98 99 100.

    Make sense? Anyone have a suggestion as to how I would go about this?

    Thanks,
    wbh
    Wild Bill Hiccup Guest

  2. #2

    Default Select a range around a specific row

    Hi,

    I need to select a range of ten rows around a specific row such that the
    specific row floats to the beginning or end of the set as the set
    approaches the beginning or end of the (ordered) table.

    So, for example, if I had a hundred rows with the values 1-100 in them,
    in no particular order, and the specific row was 2, I would need the set
    1 2 3 4 5 6 7 8 9 10. If the specific row was 20, I would need
    something more like 17 18 19 20 21 22 23 24 25 26, and if the row was
    100, I would need 91 92 93 94 95 96 97 98 99 100.

    Make sense? Anyone have a suggestion as to how I would go about this?

    Thanks,
    wbh
    Wild Bill Hiccup Guest

  3. #3

    Default Re: Select a range around a specific row

    Unfortunately, I don't think you can use variables in the LIMIT clause
    - whch would probably be necessary to make this work. It doesn't mean
    it can't be done. It just can't be done in a single elegant SELECT
    statement.

    strawberry Guest

  4. #4

    Default Re: Select a range around a specific row

    strawberry wrote:
    > Unfortunately, I don't think you can use variables in the LIMIT clause
    Correct. I recall that LIMIT accepts only literal integer constants,
    not expressions.
    > - whch would probably be necessary to make this work. It doesn't mean
    > it can't be done. It just can't be done in a single elegant SELECT
    > statement.
    On the other hand, there are probably several solutions possible in
    application code languages that are more elegant than what could be
    possible in SQL.

    Once you have an array containing all the values of the key values, app
    languages like Java, Perl, or PHP give you the ability to easily select
    elements from the array by subscript, calculate proportional position
    within the range, and display subsets of the array.

    Regards,
    Bill K.
    Bill Karwin Guest

  5. #5

    Default Re: Select a range around a specific row

    Taking Bill's suggestions to heart, here's my take on the problem. I
    don't pretend that it's elegant. I don't even swear that it's right but
    I'm showing my thinking too - in case I have gone wrong somewhere...

    The figure below is a simplified drawing of a scrollbar.

    A----------x--p------y-------------------------------------B

    A and B represent the start and end of the scrollbar
    x and y represent the beginning and end of the button on the
    scrollbar.

    (Coming back to your original enquiry x and y also represent the
    two parts of the LIMIT STATEMENT, as in "SELECT * FROM TABLE LIMIT
    x,y")

    p is the position of the button on the line AB

    Notice that p is nearer to x than it is to y. This is because p is
    nearer to A than B.

    We can say that Ap is to AB as xp is to xy

    OR

    xp = (Ap/AB) * xy

    The position of x (or Ax) on line AB is found by subtracting xp
    from Ap:

    Ax = Ap - xp

    where

    Ap is a variable integer provided by you, the user

    xp is rounded up or down to an integer value


    xy is (at least in this example) a constant integer range (the 'y'
    part of the above select statement)
    also provided by you


    and

    AB is a variable found by examining the length of the scrollbar
    (or the number of rows in a table).

    To relate this to your original enquiry,

    AB is the same as saying "SELECT COUNT(*) FROM TABLE;"

    All of that preamble serves to illustrate the meaning of each of the
    variables in the following script:

    <?php
    /* Program: pos.php
    * Desc: A Point of Scale Display
    */
    include('path/to/my/connection/script');
    //Define all the key values Bill was talking about

    $mytable = table //The data we want to use for the query
    $query = mysql_query("SELECT COUNT(*) FROM $mytable");
    //Refer to scrollbar diagram for meaning of each of the following.
    $AB = mysql_result($query,0);
    $Ap = 132;//A variable (between A & B) assigned by the user.
    $xy = 10; //A range, currently a constant
    $xp = round($xy*($Ap/$AB));
    $Ax = $Ap - $xp;
    /* Display results */

    //And so to the resulting query...

    $query = "SELECT * FROM $mytable LIMIT $Ax,$xy;";
    $result=mysql_query($query) or die ("Couldn't do it.");

    //Show the results in a bit of HTML
    echo "<table>\n";
    while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
    echo "\t<tr>\n";
    foreach ($line as $col_value) {
    echo "\t\t<td>$col_value</td>\n";
    }
    echo "\t</tr>\n";
    }
    echo "</table>\n"


    ?>

    strawberry Guest

  6. #6

    Default Re: Select a range around a specific row

    There might be a couple of mistakes in the previous script. Well, there
    usually are! I've mucked around with a tiny bit more. It's still not
    quite right - it's 1 row out I think!
    <?php
    /* Program: pos.php
    * Desc: A Point of Scale Display
    */
    //run the connection script
    include('connection/script');
    //Define all the key values Bill was talking about

    $mytable = table; //The data we want to use for the query
    $query = "SELECT COUNT(*) FROM $mytable;";
    $result = mysql_query($query) or die ("Couldn't perform 1st query.");

    //Refer to scrollbar diagram for meaning of each of the following.
    $AB = mysql_result($result,0);
    $Ap = 139;//A variable (between A & B) assigned by the user.
    $xy = 10; //A range, currently a constant
    $xp = round($xy*($Ap/$AB));
    $Ax = $Ap - $xp;

    //And so to the resulting query...

    $query = "SELECT * FROM $mytable LIMIT $xy OFFSET $Ax;";
    echo "2nd Query = $query";
    $result = mysql_query($query) or die ("Couldn't perform 2nd
    query.");

    //Show the results in a bit of HTML
    echo "<table>\n";
    while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
    echo "\t<tr>\n";
    foreach ($line as $col_value) {
    echo "\t\t<td>$col_value</td>\n";
    }
    echo "\t</tr>\n";
    }

    echo "</table>\n"

    ?>

    strawberry Guest

  7. #7

    Default Re: Select a range around a specific row

    Very cool. I can definitely run with that. Thanks so much for the help
    (and thanks to Bill Karwin as well).

    wbh

    strawberry wrote:
    > There might be a couple of mistakes in the previous script. Well, there
    > usually are! I've mucked around with a tiny bit more. It's still not
    > quite right - it's 1 row out I think!
    > <?php
    > /* Program: pos.php
    > * Desc: A Point of Scale Display
    > */
    > //run the connection script
    > include('connection/script');
    > //Define all the key values Bill was talking about
    >
    > $mytable = table; //The data we want to use for the query
    > $query = "SELECT COUNT(*) FROM $mytable;";
    > $result = mysql_query($query) or die ("Couldn't perform 1st query.");
    >
    > //Refer to scrollbar diagram for meaning of each of the following.
    > $AB = mysql_result($result,0);
    > $Ap = 139;//A variable (between A & B) assigned by the user.
    > $xy = 10; //A range, currently a constant
    > $xp = round($xy*($Ap/$AB));
    > $Ax = $Ap - $xp;
    >
    > //And so to the resulting query...
    >
    > $query = "SELECT * FROM $mytable LIMIT $xy OFFSET $Ax;";
    > echo "2nd Query = $query";
    > $result = mysql_query($query) or die ("Couldn't perform 2nd
    > query.");
    >
    > //Show the results in a bit of HTML
    > echo "<table>\n";
    > while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
    > echo "\t<tr>\n";
    > foreach ($line as $col_value) {
    > echo "\t\t<td>$col_value</td>\n";
    > }
    > echo "\t</tr>\n";
    > }
    >
    > echo "</table>\n"
    >
    > ?>
    >
    Wild Bill Hiccup Guest

  8. #8

    Default Re: Select a range around a specific row

    Your welcome. BTW, if you wanted to show which number rows you were
    currently displaying you could modify the query as follows...

    //And so to the resulting query...
    $set = "set a = $Ax;";
    $result = mysql_query($set) or die ("Couldn't perform set.");
    $query = "SELECT a := a + 1 AS row_number, client_id,
    client_name FROM $mytable LIMIT $xy OFFSET $Ax;";
    echo "2nd Query = $query";
    $result = mysql_query($query) or die ("Couldn't perform 2nd query.");

    strawberry Guest

Similar Threads

  1. Select Top - percent range?
    By Becky in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 7th, 09:38 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139