mysql query to produce the max value < 8411?

**127.0.0.1** · Jul 17 '05, 12:23 AM

Re: mysql query to produce the max value < 8411?

Phil Powell wrote:
[color=blue]
> I tried using this query:
>
> select max(revenue), bonus
> from bonuses
> where revenue < 8411
> group by revenue
>
> And it produces more than one row, which is what I don't want; I want
> only one row, the row with the maximum revenue < 8411 (in this case,
> usually a variable of course) without having to go through a rather
> lengthy table query, parsing through it and finding the max.
>
> Anyone out there know a cool mySQL or SQL trick to ensure I can only
> have one row?[/color]

Look up SQL syntax for HAVING vs WHERE....

--
Spam:newsgroup( at)craznar.com@ verisign-sux-klj.com
EMail:<01100011 001011100110001 001110101011100 10011010110
110010101000000 011000110111001 001100001011110 10011011100
110000101110010 001011100110001 101101111011011 0100100000>

**Phil Powell** · Jul 17 '05, 12:23 AM

Re: mysql query to produce the max value < 8411?

Ok using this as my reference:

fw_error_www

http://www.mysql.com/doc/en/SELECT.html

I came up with this query:

SELECT max( nnet_table_bonu s_revenue ) AS revenue, nnet_table_bonu s
FROM nnet_table_bonu s
GROUP BY nnet_table_bonu s_revenue
HAVING revenue < 8411

The query, however, still produced multiple results when I only want 1 row
returned :(

Phil
"127.0.0.1" <newsgroup(at)c raznar.com@veri sign-sux-ijlkl.com> wrote in
message news:SGpgb.1394 32$bo1.66358@ne ws-server.bigpond. net.au...[color=blue]
> Phil Powell wrote:
>[color=green]
> > I tried using this query:
> >
> > select max(revenue), bonus
> > from bonuses
> > where revenue < 8411
> > group by revenue
> >
> > And it produces more than one row, which is what I don't want; I want
> > only one row, the row with the maximum revenue < 8411 (in this case,
> > usually a variable of course) without having to go through a rather
> > lengthy table query, parsing through it and finding the max.
> >
> > Anyone out there know a cool mySQL or SQL trick to ensure I can only
> > have one row?[/color]
>
> Look up SQL syntax for HAVING vs WHERE....
>
> --
> Spam:newsgroup( at)craznar.com@ verisign-sux-klj.com
> EMail:<01100011 001011100110001 001110101011100 10011010110
> 110010101000000 011000110111001 001100001011110 10011011100
> 110000101110010 001011100110001 101101111011011 0100100000>[/color]

**127.0.0.1** · Jul 17 '05, 12:23 AM

Re: mysql query to produce the max value < 8411?

Phil Powell wrote:
[color=blue]
> The query, however, still produced multiple results when I only want
> 1 row returned :([/color]

MMmmm.... I think subqueries might be needed ... but I'm trying to get
away without them.

--
Spam:newsgroup( at)craznar.com@ verisign-sux-klj.com
EMail:<01100011 001011100110001 001110101011100 10011010110
110010101000000 011000110111001 001100001011110 10011011100
110000101110010 001011100110001 101101111011011 0100100000>

**Phil Powell** · Jul 17 '05, 12:23 AM

Re: mysql query to produce the max value < 8411?

From what I understand, subqueries can't be done in older versions of mySQL,
am I correct? this is mySQL 3.23.41 on the remote site.

Phil

"127.0.0.1" <newsgroup(at)c raznar.com@veri sign-sux-ijlkl.com> wrote in
message news:z9qgb.1394 53$bo1.23042@ne ws-server.bigpond. net.au...[color=blue]
> Phil Powell wrote:
>[color=green]
> > The query, however, still produced multiple results when I only want
> > 1 row returned :([/color]
>
> MMmmm.... I think subqueries might be needed ... but I'm trying to get
> away without them.
>
> --
> Spam:newsgroup( at)craznar.com@ verisign-sux-klj.com
> EMail:<01100011 001011100110001 001110101011100 10011010110
> 110010101000000 011000110111001 001100001011110 10011011100
> 110000101110010 001011100110001 101101111011011 0100100000>[/color]

**Dmitry Ruban** · Jul 17 '05, 12:24 AM

Re: mysql query to produce the max value < 8411?

If you're trying to find MAX(revenue) for columns with the same bonus you
have to use GROUP BY syntax, but i assume you're trying to find a record
with only maximum revenue and with appropriate bonus.

This will work fine:

SELECT
revenue, bonus
FROM bonuses
WHERE
revenue < 8411
ORDER BY revenue DESC
LIMIT 1

[color=blue]
> I tried using this query:
>
> select max(revenue), bonus
> from bonuses
> where revenue < 8411
> group by revenue
>
> And it produces more than one row, which is what I don't want; I want only
> one row, the row with the maximum revenue < 8411 (in this case, usually a
> variable of course) without having to go through a rather lengthy table
> query, parsing through it and finding the max.
>
> Anyone out there know a cool mySQL or SQL trick to ensure I can only have
> one row?
>
> Thanx
> Phil
>
>[/color]

**Jørn-Inge Kristiansen** · Jul 17 '05, 12:24 AM

Re: mysql query to produce the max value < 8411?

just thinking, if you sort by revenue, the first post would be the one with
the highest value ?

"Phil Powell" <soazine@erols. com> wrote in message
news:Riqgb.5321 4$sp2.2301@lake read04...[color=blue]
> From what I understand, subqueries can't be done in older versions of[/color]
mySQL,[color=blue]
> am I correct? this is mySQL 3.23.41 on the remote site.
>
> Phil
>
> "127.0.0.1" <newsgroup(at)c raznar.com@veri sign-sux-ijlkl.com> wrote in
> message news:z9qgb.1394 53$bo1.23042@ne ws-server.bigpond. net.au...[color=green]
> > Phil Powell wrote:
> >[color=darkred]
> > > The query, however, still produced multiple results when I only want
> > > 1 row returned :([/color]
> >
> > MMmmm.... I think subqueries might be needed ... but I'm trying to get
> > away without them.
> >
> > --
> > Spam:newsgroup( at)craznar.com@ verisign-sux-klj.com
> > EMail:<01100011 001011100110001 001110101011100 10011010110
> > 110010101000000 011000110111001 001100001011110 10011011100
> > 110000101110010 001011100110001 101101111011011 0100100000>[/color]
>
>[/color]

**127.0.0.1** · Jul 17 '05, 12:24 AM

Re: mysql query to produce the max value < 8411?

Jxrn-Inge Kristiansen wrote:
[color=blue]
> just thinking, if you sort by revenue, the first post would be the
> one with the highest value ?[/color]

But not the highest value lower than 8411 ....

Maybe sort by abs(revenue-8411) descending (-:

--
Spam:newsgroup( at)craznar.com@ verisign-sux-klj.com
EMail:<01100011 001011100110001 001110101011100 10011010110
110010101000000 011000110111001 001100001011110 10011011100
110000101110010 001011100110001 101101111011011 0100100000>

**Markus Ernst** · Jul 17 '05, 12:24 AM

Re: mysql query to produce the max value < 8411?

"Phil Powell" <soazine@erols. com> schrieb im Newsbeitrag
news:Pypgb.5284 7$sp2.41639@lak eread04...[color=blue]
> I tried using this query:
>
> select max(revenue), bonus
> from bonuses
> where revenue < 8411
> group by revenue
>
> And it produces more than one row, which is what I don't want; I want only
> one row, the row with the maximum revenue < 8411 (in this case, usually a
> variable of course) without having to go through a rather lengthy table
> query, parsing through it and finding the max.
>
> Anyone out there know a cool mySQL or SQL trick to ensure I can only have
> one row?
>
> Thanx
> Phil
>
>[/color]

SELECT revenue FROM bonuses WHERE revenue < 8411 ORDER BY revenue DESC LIMIT
1

HTH
Markus

**Phil Powell** · Jul 17 '05, 12:27 AM

Re: mysql query to produce the max value < 8411?

That was exactly it! Spasibo!

Phil

"Dmitry Ruban" <dima@region35. ru> wrote in message
news:bltmud$min $1@gavrilo.mtu. ru...[color=blue]
> If you're trying to find MAX(revenue) for columns with the same bonus you
> have to use GROUP BY syntax, but i assume you're trying to find a record
> with only maximum revenue and with appropriate bonus.
>
> This will work fine:
>
> SELECT
> revenue, bonus
> FROM bonuses
> WHERE
> revenue < 8411
> ORDER BY revenue DESC
> LIMIT 1
>
>[color=green]
> > I tried using this query:
> >
> > select max(revenue), bonus
> > from bonuses
> > where revenue < 8411
> > group by revenue
> >
> > And it produces more than one row, which is what I don't want; I want[/color][/color]
only[color=blue][color=green]
> > one row, the row with the maximum revenue < 8411 (in this case, usually[/color][/color]
a[color=blue][color=green]
> > variable of course) without having to go through a rather lengthy table
> > query, parsing through it and finding the max.
> >
> > Anyone out there know a cool mySQL or SQL trick to ensure I can only[/color][/color]
have[color=blue][color=green]
> > one row?
> >
> > Thanx
> > Phil
> >
> >[/color]
>
>[/color]

mysql query to produce the max value < 8411?

mysql query to produce the max value < 8411?

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment