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
firstname@lastname.net

On Feb 20, 7:29 am, krist...@bommelibom.com wrote:
> 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
> firstn...@lastname.net

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.

On Feb 20, 10:32 am, "strawberry" <zac.ca...@gmail.com> wrote:
> On Feb 20, 7:29 am, krist...@bommelibom.com wrote:
>
>
>
> > 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
> > firstn...@lastname.net
>
> 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.

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.

On Feb 20, 2:30 pm, krist...@bommelibom.com wrote:
> On Feb 20, 10:32 am, "strawberry" <zac.ca...@gmail.com> wrote:
>
>
>
> > On Feb 20, 7:29 am, krist...@bommelibom.com wrote:
>
> > > 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
> > > firstn...@lastname.net
>
> > 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.
>
> 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.

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

On Feb 20, 1:41 pm, krist...@bommelibom.com wrote:
> On Feb 20, 2:30 pm, krist...@bommelibom.com wrote:
>
>
>
> > On Feb 20, 10:32 am, "strawberry" <zac.ca...@gmail.com> wrote:
>
> > > On Feb 20, 7:29 am, krist...@bommelibom.com wrote:
>
> > > > 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
> > > > firstn...@lastname.net
>
> > > 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.
>
> > 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.
>
> 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

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

On 20 Feb, 13:41, krist...@bommelibom.com wrote:
> On Feb 20, 2:30 pm, krist...@bommelibom.com wrote:
>
>
>
>
>
> > On Feb 20, 10:32 am, "strawberry" <zac.ca...@gmail.com> wrote:
>
> > > On Feb 20, 7:29 am, krist...@bommelibom.com wrote:
>
> > > > 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
> > > > firstn...@lastname.net
>
> > > 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.
>
> > 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.
>
> 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 -

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?

On 20 Feb, 13:41, krist...@bommelibom.com wrote:
> On Feb 20, 2:30 pm, krist...@bommelibom.com wrote:
>
>
>
>
>
> > On Feb 20, 10:32 am, "strawberry" <zac.ca...@gmail.com> wrote:
>
> > > On Feb 20, 7:29 am, krist...@bommelibom.com wrote:
>
> > > > 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
> > > > firstn...@lastname.net
>
> > > 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.
>
> > 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.
>
> 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 -

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.

On Feb 20, 4:11 pm, "Captain Paralytic" <paul_laut...@yahoo.com>
wrote:
> On 20 Feb, 13:41, krist...@bommelibom.com wrote:
>
>
>
> > On Feb 20, 2:30 pm, krist...@bommelibom.com wrote:
>
> > > On Feb 20, 10:32 am, "strawberry" <zac.ca...@gmail.com> wrote:
>
> > > > On Feb 20, 7:29 am, krist...@bommelibom.com wrote:
>
> > > > > 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
> > > > > firstn...@lastname.net
>
> > > > 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.
>
> > > 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.
>
> > 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 -
>
> 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.


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.