The Cache: Technology Expert's Forum
 
*
Welcome, Guest. Please login or register. October 16, 2019, 06:00:21 AM

Login with username, password and session length


Pages: [1] 2
  Print  
Author Topic: MySQL - finding the row with the most in [x] column  (Read 9186 times)
perkiset
Olde World Hacker
Administrator
Lifer
*****
Offline Offline

Posts: 10096



View Profile
« on: July 15, 2008, 07:49:03 PM »

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?
Logged

It is now believed, that after having lived in one compound with 3 wives and never leaving the house for 5 years, Bin Laden called the U.S. Navy Seals himself.
nutballs
Administrator
Lifer
*****
Offline Offline

Posts: 5627


Back in my day we had 9 planets


View Profile
« Reply #1 on: July 15, 2008, 10:18:14 PM »

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.
Logged

I could eat a bowl of Alphabet Soup and shit a better argument than that.
perkiset
Olde World Hacker
Administrator
Lifer
*****
Offline Offline

Posts: 10096



View Profile
« Reply #2 on: July 15, 2008, 10:24:33 PM »

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... Wink
Logged

It is now believed, that after having lived in one compound with 3 wives and never leaving the house for 5 years, Bin Laden called the U.S. Navy Seals himself.
nutballs
Administrator
Lifer
*****
Offline Offline

Posts: 5627


Back in my day we had 9 planets


View Profile
« Reply #3 on: July 16, 2008, 06:47:52 AM »

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...
Logged

I could eat a bowl of Alphabet Soup and shit a better argument than that.
nutballs
Administrator
Lifer
*****
Offline Offline

Posts: 5627


Back in my day we had 9 planets


View Profile
« Reply #4 on: July 16, 2008, 07:49:37 AM »

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

I could eat a bowl of Alphabet Soup and shit a better argument than that.
perkiset
Olde World Hacker
Administrator
Lifer
*****
Offline Offline

Posts: 10096



View Profile
« Reply #5 on: July 16, 2008, 11:07:19 AM »

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.
Logged

It is now believed, that after having lived in one compound with 3 wives and never leaving the house for 5 years, Bin Laden called the U.S. Navy Seals himself.
nutballs
Administrator
Lifer
*****
Offline Offline

Posts: 5627


Back in my day we had 9 planets


View Profile
« Reply #6 on: July 16, 2008, 11:10:19 AM »

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.
Logged

I could eat a bowl of Alphabet Soup and shit a better argument than that.
perkiset
Olde World Hacker
Administrator
Lifer
*****
Offline Offline

Posts: 10096



View Profile
« Reply #7 on: July 16, 2008, 11:20:25 AM »

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.
Logged

It is now believed, that after having lived in one compound with 3 wives and never leaving the house for 5 years, Bin Laden called the U.S. Navy Seals himself.
nutballs
Administrator
Lifer
*****
Offline Offline

Posts: 5627


Back in my day we had 9 planets


View Profile
« Reply #8 on: July 16, 2008, 11:48:05 AM »

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

I could eat a bowl of Alphabet Soup and shit a better argument than that.
dbdog
Moderator
Lifer
*****
Offline Offline

Posts: 734



View Profile
« Reply #9 on: July 17, 2008, 08:05:45 AM »

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
Logged
dbdog
Moderator
Lifer
*****
Offline Offline

Posts: 734



View Profile
« Reply #10 on: July 17, 2008, 08:38:32 AM »

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
Logged
nutballs
Administrator
Lifer
*****
Offline Offline

Posts: 5627


Back in my day we had 9 planets


View Profile
« Reply #11 on: July 17, 2008, 09:25:29 AM »

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.
Logged

I could eat a bowl of Alphabet Soup and shit a better argument than that.
perkiset
Olde World Hacker
Administrator
Lifer
*****
Offline Offline

Posts: 10096



View Profile
« Reply #12 on: July 17, 2008, 10:07:23 AM »

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

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.
Logged

It is now believed, that after having lived in one compound with 3 wives and never leaving the house for 5 years, Bin Laden called the U.S. Navy Seals himself.
perkiset
Olde World Hacker
Administrator
Lifer
*****
Offline Offline

Posts: 10096



View Profile
« Reply #13 on: July 17, 2008, 10:18:15 AM »

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...

« Last Edit: July 17, 2008, 10:20:42 AM by perkiset » Logged

It is now believed, that after having lived in one compound with 3 wives and never leaving the house for 5 years, Bin Laden called the U.S. Navy Seals himself.
nutballs
Administrator
Lifer
*****
Offline Offline

Posts: 5627


Back in my day we had 9 planets


View Profile
« Reply #14 on: July 17, 2008, 11:01:26 AM »

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.
Logged

I could eat a bowl of Alphabet Soup and shit a better argument than that.
Pages: [1] 2
  Print  
 
Jump to:  

Perkiset's Place Home   Best of The Cache   phpMyIDE: MySQL Stored Procedures, Functions & Triggers
Politics @ Perkiset's   Pinkhat's Perspective   
cache
mart
coder
programmers
ajax
php
javascript
Powered by MySQL Powered by PHP Powered by SMF 1.1.2 | SMF © 2006-2007, Simple Machines LLC
Seo4Smf v0.2 © Webmaster's Talks


Valid XHTML 1.0! Valid CSS!