mysql query to produce the max value < 8411?

[Phil Powell]

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

[127.0.0.1]

Phil Powell wrote:

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?

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

--
Spam:newsgroup(at)cr*********@verisign-sux-klj.com
EMail:<0110001100101110011000100111010101110010011 010110
11001010100000001100011011100100110000101111010011 011100
11000010111001000101110011000110110111101101101001 00000>

[Phil Powell]

Ok using this as my reference:

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

I came up with this query:

SELECT max( nnet_table_bonus_revenue ) AS revenue, nnet_table_bonus
FROM nnet_table_bonus
GROUP BY nnet_table_bonus_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)cr*********@verisign-sux-ijlkl.com> wrote in
message news:SG********************@news-server.bigpond.net.au...

Phil Powell wrote:

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?

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

--
Spam:newsgroup(at)cr*********@verisign-sux-klj.com
EMail:<0110001100101110011000100111010101110010011 010110
11001010100000001100011011100100110000101111010011 011100
11000010111001000101110011000110110111101101101001 00000>

[127.0.0.1]

Phil Powell wrote:

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

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

--
Spam:newsgroup(at)cr*********@verisign-sux-klj.com
EMail:<0110001100101110011000100111010101110010011 010110
11001010100000001100011011100100110000101111010011 011100
11000010111001000101110011000110110111101101101001 00000>

[Phil Powell]

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)cr*********@verisign-sux-ijlkl.com> wrote in
message news:z9********************@news-server.bigpond.net.au...

Phil Powell wrote:

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

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

--
Spam:newsgroup(at)cr*********@verisign-sux-klj.com
EMail:<0110001100101110011000100111010101110010011 010110
11001010100000001100011011100100110000101111010011 011100
11000010111001000101110011000110110111101101101001 00000>

[Dmitry Ruban]

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

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

[Jørn-Inge Kristiansen]

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

"Phil Powell" <so*****@erols.com> wrote in message
news:Riqgb.53214$sp2.2301@lakeread04...

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)cr*********@verisign-sux-ijlkl.com> wrote in
message news:z9********************@news-server.bigpond.net.au...

Phil Powell wrote:

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

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

--
Spam:newsgroup(at)cr*********@verisign-sux-klj.com
EMail:<0110001100101110011000100111010101110010011 010110
11001010100000001100011011100100110000101111010011 011100
11000010111001000101110011000110110111101101101001 00000>

[127.0.0.1]

Jxrn-Inge Kristiansen wrote:

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

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

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

--
Spam:newsgroup(at)cr*********@verisign-sux-klj.com
EMail:<0110001100101110011000100111010101110010011 010110
11001010100000001100011011100100110000101111010011 011100
11000010111001000101110011000110110111101101101001 00000>

[Markus Ernst]

"Phil Powell" <so*****@erols.com> schrieb im Newsbeitrag
news:Pypgb.52847$sp2.41639@lakeread04...

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

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

HTH
Markus

[Phil Powell]

That was exactly it! Spasibo!

Phil

"Dmitry Ruban" <di**@region35.ru> wrote in message
news:bl**********@gavrilo.mtu.ru...

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

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