Professional Web Applications Themes

using max() and returning the corresponding rows... - MySQL

Hi all Lets say I have a table with: key, groupID, valueOfInterest and sortingValue, all ints. I want the valueOfInterest where sortingValue is as large as possible, but limited by 10 (or some value i calculate...) I was pretty sure I managed this earlier by: SELECT key, groupID, valueOfInterest, sortingValue, MAX(sortingValue) FROM table WHERE sortingValue<10 GROUP BY groupID; But this seems to return some random row and the max-values. Instead I have come up with: SELECT key, groupID, valueOfInterest, sortingValue FROM table WHERE sortingValue IN (SELECT MAX(sortingValue) FROM table WHERE sortingValue sortingValue < 10 GROUP BY groupID); Is there a ...

  1. #1

    Default using max() and returning the corresponding rows...

    Hi all

    Lets say I have a table with: key, groupID, valueOfInterest and
    sortingValue, all ints.
    I want the valueOfInterest where sortingValue is as large as possible,
    but limited by 10 (or some value i calculate...)

    I was pretty sure I managed this earlier by:

    SELECT key, groupID, valueOfInterest, sortingValue, MAX(sortingValue)
    FROM table WHERE sortingValue<10 GROUP BY groupID;

    But this seems to return some random row and the max-values. Instead I
    have come up with:

    SELECT key, groupID, valueOfInterest, sortingValue FROM table WHERE
    sortingValue IN (SELECT MAX(sortingValue) FROM table WHERE
    sortingValue sortingValue < 10 GROUP BY groupID);

    Is there a way of doing this without the subquery? I must admit I
    don't know if the second query always will return what I want...

    --
    Kristian Svartveit
    net

    kristian@bommelibom.com Guest

  2. #2

    Default Re: using max() and returning the corresponding rows...

    On Feb 20, 7:29 am, com wrote: 

    This is a groupwise maximum query, the generic form of which might
    look something like this:

    SELECT t1. *
    FROM `mytable` t1
    LEFT JOIN `mytable` t2 ON t1.`primary_key_field` <>
    t2.`pimary_key_field`
    AND t1.`grouping_field` = t2.`grouping_field`
    AND t1.`field_for_comparison` < t2.`field_for_comparison`
    WHERE
    t2.`field_for_comparison` IS NULL
    ORDER BY t1.`grouping_field`

    and into whose WHERE clause you could also insert a condition
    something like this:

    AND t1.`field_for_comparison` < 10

    Also have a look for Strawberry query in these NGs.

    strawberry Guest

  3. #3

    Default Re: using max() and returning the corresponding rows...

    On Feb 20, 10:32 am, "strawberry" <com> wrote: 







    >
    > This is a groupwise maximum query, the generic form of which might
    > look something like this:
    >
    > SELECT t1. *
    > FROM `mytable` t1
    > LEFT JOIN `mytable` t2 ON t1.`primary_key_field` <>
    > t2.`pimary_key_field`
    > AND t1.`grouping_field` = t2.`grouping_field`
    > AND t1.`field_for_comparison` < t2.`field_for_comparison`
    > WHERE
    > t2.`field_for_comparison` IS NULL
    > ORDER BY t1.`grouping_field`
    >
    > and into whose WHERE clause you could also insert a condition
    > something like this:
    >
    > AND t1.`field_for_comparison` < 10
    >
    > Also have a look for Strawberry query in these NGs.[/ref]

    Thank you for helping, but it doesn't seem to work when I insert the
    extra WHERE clause on the comparison field.


    Here is a short code creating the example:

    DROP TABLE IF EXISTS mytable;
    CREATE TABLE mytable (
    mykey INT NOT NULL PRIMARY KEY,
    mygroup INT NOT NULL,
    mytime double);

    INSERT INTO mytable (mykey, mygroup, mytime) VALUES
    (1,1,0),
    (2,1,2),
    (3,1,4),
    (4,1,6),
    (5,2,0),
    (6,2,2),
    (7,2,4),
    (8,2,6),
    (9,2,8);

    SELECT t1.*
    FROM mytable t1
    LEFT JOIN mytable t2
    ON t1.mygroup = t2.mygroup AND t1.mytime < t2.mytime AND t1.mytime < 7
    WHERE t2.mytime IS NULL ;

    The above query will here find, groupwise, the records with the
    highest time(what you call the strawberry query). I want the highest
    time, when time is constrained, that is: The highest time below a
    limit. I tried with

    SELECT t1.*
    FROM mytable t1
    LEFT JOIN mytable t2
    ON t1.mygroup = t2.mygroup AND t1.mytime < t2.mytime AND t1.mytime < 7
    WHERE t2.mytime IS NULL AND t1.mytime < 5;

    But that returns an empty set.

    --
    K
    PS: I must say that I totally don't get the inner workings of the
    strawberry query and would love a link to somewhere it is well
    explained.






    kristian@bommelibom.com Guest

  4. #4

    Default Re: using max() and returning the corresponding rows...

    On Feb 20, 2:30 pm, com wrote: 
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]





    >
    > Thank you for helping, but it doesn't seem to work when I insert the
    > extra WHERE clause on the comparison field.
    >
    > Here is a short code creating the example:
    >
    > DROP TABLE IF EXISTS mytable;
    > CREATE TABLE mytable (
    > mykey INT NOT NULL PRIMARY KEY,
    > mygroup INT NOT NULL,
    > mytime double);
    >
    > INSERT INTO mytable (mykey, mygroup, mytime) VALUES
    > (1,1,0),
    > (2,1,2),
    > (3,1,4),
    > (4,1,6),
    > (5,2,0),
    > (6,2,2),
    > (7,2,4),
    > (8,2,6),
    > (9,2,8);
    >
    > SELECT t1.*
    > FROM mytable t1
    > LEFT JOIN mytable t2
    > ON t1.mygroup = t2.mygroup AND t1.mytime < t2.mytime AND t1.mytime < 7
    > WHERE t2.mytime IS NULL ;
    >
    > The above query will here find, groupwise, the records with the
    > highest time(what you call the strawberry query). I want the highest
    > time, when time is constrained, that is: The highest time below a
    > limit. I tried with
    >
    > SELECT t1.*
    > FROM mytable t1
    > LEFT JOIN mytable t2
    > ON t1.mygroup = t2.mygroup AND t1.mytime < t2.mytime AND t1.mytime < 7
    > WHERE t2.mytime IS NULL AND t1.mytime < 5;
    >
    > But that returns an empty set.
    >
    > --
    > K
    > PS: I must say that I totally don't get the inner workings of the
    > strawberry query and would love a link to somewhere it is well
    > explained.[/ref]

    Oops the extra AND t1.mytime < 7 in the join on statement shouldn't be
    there, that was just something else I tested...


    SELECT t1.*
    FROM mytable t1
    LEFT JOIN mytable t2
    ON t1.mygroup = t2.mygroup AND t1.mytime < t2.mytime
    WHERE t2.mytime IS NULL ;

    which returns the last, and


    SELECT t1.*
    FROM mytable t1
    LEFT JOIN mytable t2
    ON t1.mygroup = t2.mygroup AND t1.mytime < t2.mytime
    WHERE t2.mytime IS NULL AND t1.mytime < 5;

    which I wanted to return last below 5 should be the correct examples.

    --
    K

    kristian@bommelibom.com Guest

  5. #5

    Default Re: using max() and returning the corresponding rows...

    On Feb 20, 1:41 pm, com wrote: 
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]









    >
    > Oops the extra AND t1.mytime < 7 in the join on statement shouldn't be
    > there, that was just something else I tested...
    >
    > SELECT t1.*
    > FROM mytable t1
    > LEFT JOIN mytable t2
    > ON t1.mygroup = t2.mygroup AND t1.mytime < t2.mytime
    > WHERE t2.mytime IS NULL ;
    >
    > which returns the last, and
    >
    > SELECT t1.*
    > FROM mytable t1
    > LEFT JOIN mytable t2
    > ON t1.mygroup = t2.mygroup AND t1.mytime < t2.mytime
    > WHERE t2.mytime IS NULL AND t1.mytime < 5;
    >
    > which I wanted to return last below 5 should be the correct examples.
    >
    > --
    > K[/ref]

    Adapted from Bill Karwin's description at http://tinyurl.com/32v5yu:

    "Show me the row where there is no other row with the
    same group id and a comparative field of greater value."

    With regards the rest of your post, now I'm a bit confused.

    Would you mind reposting the table, the sample data, and the result
    you'd like to obtain?

    Regards,

    Zac


    strawberry Guest

  6. #6

    Default Re: using max() and returning the corresponding rows...

    On 20 Feb, 13:41, com wrote: 
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]









    >
    > Oops the extra AND t1.mytime < 7 in the join on statement shouldn't be
    > there, that was just something else I tested...
    >
    > SELECT t1.*
    > FROM mytable t1
    > LEFT JOIN mytable t2
    > ON t1.mygroup = t2.mygroup AND t1.mytime < t2.mytime
    > WHERE t2.mytime IS NULL ;
    >
    > which returns the last, and
    >
    > SELECT t1.*
    > FROM mytable t1
    > LEFT JOIN mytable t2
    > ON t1.mygroup = t2.mygroup AND t1.mytime < t2.mytime
    > WHERE t2.mytime IS NULL AND t1.mytime < 5;
    >
    > which I wanted to return last below 5 should be the correct examples.
    >
    > --
    > K- Hide quoted text -
    >
    > - Show quoted text -[/ref]

    Can you clear something up for me, taking the sample data that you
    supplied, if your limit was 7, what would you expect to see as the
    output?

    Captain Guest

  7. #7

    Default Re: using max() and returning the corresponding rows...

    On 20 Feb, 13:41, com wrote: 
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]









    >
    > Oops the extra AND t1.mytime < 7 in the join on statement shouldn't be
    > there, that was just something else I tested...
    >
    > SELECT t1.*
    > FROM mytable t1
    > LEFT JOIN mytable t2
    > ON t1.mygroup = t2.mygroup AND t1.mytime < t2.mytime
    > WHERE t2.mytime IS NULL ;
    >
    > which returns the last, and
    >
    > SELECT t1.*
    > FROM mytable t1
    > LEFT JOIN mytable t2
    > ON t1.mygroup = t2.mygroup AND t1.mytime < t2.mytime
    > WHERE t2.mytime IS NULL AND t1.mytime < 5;
    >
    > which I wanted to return last below 5 should be the correct examples.
    >
    > --
    > K- Hide quoted text -
    >
    > - Show quoted text -[/ref]

    Tell you what, try this one and see if it works.

    SELECT t1. *
    FROM mytable t1
    LEFT JOIN mytable t2 ON t1.mygroup = t2.mygroup
    AND t1.mytime < t2.mytime
    AND t2.mytime <7
    WHERE t2.mytime IS NULL
    HAVING t1.mytime <7

    If you want to change the constraint to 5, you'll need to do it by
    changing both the 7s to 5s, not just one of them.

    Captain Guest

  8. #8

    Default Re: using max() and returning the corresponding rows...

    On Feb 20, 4:11 pm, "Captain Paralytic" <com>
    wrote: 
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]







    >
    > Tell you what, try this one and see if it works.
    >
    > SELECT t1. *
    > FROM mytable t1
    > LEFT JOIN mytable t2 ON t1.mygroup = t2.mygroup
    > AND t1.mytime < t2.mytime
    > AND t2.mytime <7
    > WHERE t2.mytime IS NULL
    > HAVING t1.mytime <7
    >
    > If you want to change the constraint to 5, you'll need to do it by
    > changing both the 7s to 5s, not just one of them.[/ref]


    Thanks a bunch, that did the trick. I had tried one and the other, but
    not the combination.

    --
    K
    PS: Some more thinking and writing and testing it made the strawberry
    clear enough for me to get it.

    kristian@bommelibom.com Guest

Similar Threads

  1. Grouping and returning rows
    By UKuser in forum MySQL
    Replies: 7
    Last Post: January 10th, 04:55 PM
  2. ODBC Sybase driver returning zero rows
    By ColonelNusterd in forum Coldfusion Database Access
    Replies: 1
    Last Post: April 18th, 01:55 PM
  3. Returning 0 rows from a PL/PGSQL
    By Vitaly in forum PostgreSQL / PGSQL
    Replies: 2
    Last Post: February 20th, 09:59 PM
  4. Returning Data Rows from WS in different Formats Problem
    By Ray Parker in forum ASP.NET Web Services
    Replies: 0
    Last Post: July 2nd, 07:02 AM
  5. returning results in rows
    By Ken in forum ASP
    Replies: 2
    Last Post: September 17th, 02:15 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