MySQL – Get maximum Tuple without Grouping everything

MySQL

I need some help by writing a SQL statement.

I have a table (it's already a result from another query) with 4 columns e.g.:

Firstname | Lastname | Softwarename | Hours

With a example data set like:

Row1: Max | Huber | Word | 25h

Row2: Max | Huber | Excel | 12h

The result of the query should be the first row.
My problem is that I am forced to add the Softwarename to the group by, but when I do that the result is obviously incorrect.

How do I do that?

Thank you!

Best Answer

If I've understood you (at last) correctly, I think you want something like this (see fiddle here):

Create your table:

CREATE TABLE test_tab
(
  first_name VARCHAR (25) NOT NULL,
  last_name  VARCHAR (25) NOT NULL,
  soft_name  VARCHAR (25) NOT NULL,
  soft_hours VARCHAR (25) NOT NULL
);

Populate it:

INSERT INTO test_tab VALUES ('Max', 'Huber', 'Word',  '25h');
INSERT INTO test_tab VALUES ('Max', 'Huber', 'Excel', '15h');

INSERT INTO test_tab VALUES ('Felix', 'TheCat', 'Outlook', '35h');
INSERT INTO test_tab VALUES ('Felix', 'TheCat', 'Word',  '25h');
INSERT INTO test_tab VALUES ('Felix', 'TheCat', 'Excel', '15h');

INSERT INTO test_tab VALUES ('Donald', 'Duck', 'Excel', '20h');
INSERT INTO test_tab VALUES ('Donald', 'Duck', 'Word',  '8h');
INSERT INTO test_tab VALUES ('Donald', 'Duck', 'Outlook', '3h');

And use the following premliminary SQL (included to demonstrate the thought processes that went into formulating the answer. I find that people learn better if they can see a "breadcrumb trail" (cf. Hansel and Gretel)):

SELECT
  first_name,
  last_name,
  MAX(CAST(soft_hours AS UNSIGNED)) AS use_duration
FROM test_tab
GROUP BY first_name, last_name
ORDER BY last_name;

The CAST is necessary because of the inclusion of the hin the string for the duration of usage (much better to use a plain INTEGER). If the CAST isn't present, 8h will be "greater than" 20h for Donald Duck when sorted as a character string - CASTing to UNSIGNED removes that problem.

Result:

first_name  last_name   use_duration
    Donald       Duck             20
       Max      Huber             25
     Felix     TheCat             35

Having obtained the first and last names and the use_duration, we are now in a position to JOIN back to our original table this way:

SELECT 
  t1.first_name, t1.last_name, t1.soft_name, 
  CAST(t1.soft_hours AS UNSIGNED) AS "usage"
FROM test_tab t1
JOIN
(
  SELECT 
    first_name,
    last_name,
    MAX(CAST(soft_hours AS UNSIGNED)) AS use_duration
  FROM test_tab
  GROUP BY first_name, last_name
) AS t2
ON t1.first_name = t2.first_name AND t1.last_name = t2.last_name
AND CAST(t1.soft_hours AS UNSIGNED) = t2.use_duration
ORDER BY t1.last_name;

Result:

first_name  last_name   soft_name   usage
    Donald       Duck       Excel      20
       Max      Huber        Word      25
     Felix     TheCat     Outlook      35

Now, I would recommend that you keep the duration as just a pure INTEGER rather than use 25h. I hope this responds to your needs, - if not, then please expand on your explanation and I'll endeavour to revise my answer to your satisfaction. p.s. welcome to the forum! :-)

Related Question