MySQL - Get maximum Tuple without Grouping everything

Question

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!

Answer 1

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! :-)