Scott Klarenbach <[EMAIL PROTECTED]> wrote on 05/26/2005 04:25:22
PM:
> Can I select the maximum value across multiple columns?
>
> ie, I'd like to select the highest value of buyCost AND sellCost in a
> table...where buy and sell are two different columns in the same
> table.
>
> i actually have 4 comparisons to run, and don't want to have to
> execute 4 queries.
>
Short answer: YES
Long answer:
Let's imagine you are running a message board and you wanted to know some
statistics about how busy your board is. Continue to imagine that you
store each message as a row in a table called messages:
CREATE TABLE messages (
ID int unsigned auto_increment primary key
, from_user_id int unsigned
, to_user_id int unsigned
, msgdate datetime
, msgsize int unsigned
, msg TEXT
);
To get some absolute information about messages (all messages as one
group):
SELECT min(msg_date) as firstmsg
, max(msg_date) as lastmsg
, min(msg_size) as smallestmsg
, max(msg_size) as longestmsg
, avg(msg_size) as avgmsg
, sum(msg_size) as totalmsg
, count(msg_size) as msgcount
FROM messages
GROUP BY from_user_id;
To get the same information but break it down by the user sending the
message:
SELECT from_user_id
, min(msg_date) as firstmsg
, max(msg_date) as lastmsg
, min(msg_size) as smallestmsg
, max(msg_size) as longestmsg
, avg(msg_size) as avgmsg
, sum(msg_size) as totalmsg
, count(msg_size) as msgcount
FROM messages
GROUP BY from_user_id;
Were these the types of queries you were interested in?
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
