next »

[perkiset]

I know I must just be being an idiot here... but I can't seem to get the row of a table that has the largest value in a particular column.

Clarification - I can do it with 2 queries easily, it's doing it in a single query that's buggering me.

So, I have:

----------------------------------------------------------------------------------------------------------------
| item_id | kind | code   | active | sort_order | caption     | multiplier | addition | image_name | weightedQ |
----------------------------------------------------------------------------------------------------------------
| B10     | c    | BK     | 1      | 0          | Black       | 0          | 0        |            | 57.5      |
| B10     | c    | BL     | 1      | 0          | Blue        | 0          | 0        |            | 26        |
| B10     | c    | IV     | 1      | 0          | Ivory       | 0          | 0        |            | 153.5     |
| B10     | c    | TL     | 1      | 0          | Teal        | 0          | 0        |            | 103.5     |
| B10     | c    | TN     | 1      | 0          | Tan         | 0          | 0        |            | 131.5     |
| B10     | s    | L      | 1      | 0          | Large       | 0          | 0        |            | 0         |
| B10     | s    | M      | 1      | 0          | Medium      | 0          | 0        |            | 0         |
| B10     | s    | S      | 1      | 0          | Small       | 0          | 0        |            | 0         |
| B10     | s    | XL     | 1      | 0          | X-Large     | 0          | 0        |            | 0         |
| B10     | s    | XXL    | 1      | 0          | XX-Large    | 0          | 0        |            | 0         |
----------------------------------------------------------------------------------------------------------------

I want to find the row with the largest weightedQ. So I:

select code, caption, max(weightedQ) from item_choices where item_id='B10' group by item_id

But this gives me:

-------------------------------------
| code  | caption  | max(weightedQ) |
-------------------------------------
| BK    | Black    | 153.5          |
-------------------------------------

... the max function seems to be disassociated from the caption column - MySQL is simply selecting the first row values for code and caption, then the max(weightedQ) value and returning all of that mess as a single result.

In a stored procedure I have scanned for the largest number, then simply queried for the rest of the row that has <that> number... but that's pretty ugly IMO.

Any thoughts?

[nutballs]

select * from table where thing = max(thing)

that was a 5 second, no thought applied, tired and getting ready for bed answer. it may be completely wrong. obviously modify to suit, but you get the point.

[perkiset]

LOL well then off to bed with you...

problem is I don't know what (left side) thing is to equate it to (right side) thing - in effect though, this is what the second query does in my stored procedure. The first query has to find the max(thing) first.

Thanks though for your Hail Mary from the bleachers just before beddyby time...

[nutballs]

Now that Im awake i see a few things

max() is disassociated like you found out.
select fname,lname,max(logins) from somebigasstable
will return every user, with the same number in the third column

so this doesnt work for you (i used false names to protect the innocent in my first post)?
select code, caption, weightedQ from item_choices where item_id='B10' and weightedQ = max(weightedQ)
OR
select code, caption, weightedQ from item_choices where item_id='B10' and weightedQ = (select max(weightedQ) from item_choices where item_id='B10' limit 1)

not positive about needing the limit 1 on the second option. not testing any of this. And yes, they second option is 2 queries technically.

it looks like you already know which item_id you want, and its a matter of finding the max weightedQ from those entries matching that item. If there is something else you neglected to post...

[nutballs]

BTW ping me if you need to talk it through or the details are a bit more effort than typing.

[perkiset]

thanks nuts... forgot about the subquery method, which may be a titch faster because it'll be prepared and handled in one pass. I'll give'r a rip... I've got it working fine with a 2-query method in a stored procedure (that's where all of this happens anyway) but I think the subquery is the best looking solution.

[nutballs]

so the
select code, caption, weightedQ from item_choices where item_id='B10' and weightedQ = max(weightedQ)
doesnt work?

is it because of a syntax issue in mysql? or is it a 'you don't know something' that I dont know about?

just curious.

[perkiset]

grouping functions like max and sum can't be used on the right side like that AFAIK ... I just tried it to confirm and got that very error... there might be another way to do it, but I don't know.

[nutballs]

ok, i wasnt sure, but figured that was why you werent doing that. I seem to remember doing that in msSQL, but maybe not.

[dbdog]

Perk, Check out the "HAVING" clause
After the "group by" you can say "having <more where conditions here>"

Brain still foggy...need some coffee...will be back with more in a bit...

ARF

[dbdog]

From MySQL 5 reference:

The HAVING clause can refer to aggregate functions, which the WHERE clause cannot:

SELECT user, MAX(salary) FROM users
  GROUP BY user HAVING MAX(salary) > 10;

(This did not work in some older versions of MySQL.)

If so will this work......

select code, caption, weightedQ from item_choices where item_id='B10' having weightedQ = max(weightedQ)

ARF

[nutballs]

BAH the profession DB guy comes to the rescue. I was missing the HAVING part. lol
completely forgot that bit. duh.
thats why i was confused that it wasnt working.

[perkiset]

suhWEETNESS Dog, thanks so much. I had hoped that maybe you'd weigh in

Excellent reference point to start a little personal research as well, since I was unaware of the HAVING keyword at all.

Now go getchasum coffee mang.

[perkiset]

OK - I get no errors from the above, but no rows either. These examples are from phpMyIDE, but I get the same results in phpMyAdmin.

For references:

select item_id, code, caption, weightedQ from item_choices where item_id='B10'
----------------------------------------------
| item_id | code   | caption     | weightedQ |
----------------------------------------------
| B10     | BK     | Black       | 57.5      |
| B10     | BL     | Blue        | 26        |
| B10     | IV     | Ivory       | 153.5     |
| B10     | TL     | Teal        | 103.5     |
| B10     | TN     | Tan         | 131.5     |
| B10     | L      | Large       | 0         |
| B10     | M      | Medium      | 0         |
| B10     | S      | Small       | 0         |
| B10     | XL     | X-Large     | 0         |
| B10     | XXL    | XX-Large    | 0         |
----------------------------------------------
Number of rows returned: 10

And then the query:

select code, caption, weightedQ from item_choices where item_id='B10' having weightedQ= max(weightedQ)
*** No rows returned ***

Looking through the MySQL docs on Group By and Having I noticed that they talk a lot about having both in a query, so I did not know if that was a requirement and tried this:

select item_id, code, caption, weightedQ from item_choices where item_id='B10' 
group by weightedQ having weightedQ = max(weightedQ)
------------------------------------------
| item_id | code  | caption  | weightedQ |
------------------------------------------
| B10     | L     | Large    | 0         |
| B10     | BL    | Blue     | 26        |
| B10     | BK    | Black    | 57.5      |
| B10     | TL    | Teal     | 103.5     |
| B10     | TN    | Tan      | 131.5     |
| B10     | IV    | Ivory    | 153.5     |
------------------------------------------
Number of rows returned: 6

... which is interesting, but of course not what I am looking for unless I do the following:

select item_id, code, caption, weightedQ from item_choices where item_id='B10' 
group by weightedQ having weightedQ = max(weightedQ) order by weightedQ DESC limit 1
------------------------------------------
| item_id | code  | caption  | weightedQ |
------------------------------------------
| B10     | IV    | Ivory    | 153.5     |
------------------------------------------
Number of rows returned: 1

... which looks WAY overkill, and in fact should be more simple like:

select item_id, code, caption, weightedQ from item_choices where item_id='B10' order by weightedQ DESC limit 1
------------------------------------------
| item_id | code  | caption  | weightedQ |
------------------------------------------
| B10     | IV    | Ivory    | 153.5     |
------------------------------------------
Number of rows returned: 1

... voila, I have my answer. But what am I missing in the HAVING clause? It looks so promising...

[nutballs]

you know i just realized why the HAVING approach wont work.

HAVING is evaluated on a per row basis i think, not a tablewide basis. its basically a true/false test. So when you run this query:
select code, caption, weightedQ
from item_choices
having max(weightedQ)

you get the first record, always. because, at the row level, that first row has the MAX(weightedQ).

group by, btw is table level, all rows are evaluated (at least that pass the HAVING and WHERE clauses and whatever else), and combines the results into as few results as possible, without losing data. Thats why you cannot do:
select * from table group by randomfield
You have to group by a field named in the select list. exception being aggregates in the select list of course, but they just repeat regardless of the result.

So...

I think you HAVE to do a subselect
select code, caption, weightedQ from item_choices where weightedQ = (select max(weightedQ) from item_choices)

I dont think there is any other way to do it, other than the 'order by weightedQ desc' which you pointed out at the end, which caused me a big giant DUH. both methods do the same exact thing, but i have a feeling ORDER BY is slower than MAX(). not sure though.