Why is the tuple with minimum date not shown?

The truth is that it is a rather confusing subject. I hope I can clarify it in a simple way.

I start with the solution:

SELECT H FROM Hospital WHERE PR='Madrid'
  AND F=(
    SELECT MIN(F) FROM Hospital WHERE PR='Madrid'
  );

And the dataset from which I started, so that the checks can be carried out:

CREATE TABLE Hospital(
  H int AUTO_INCREMENT PRIMARY KEY,
  PR varchar(10),
  F date
  );
INSERT INTO Hospital (PR, F) VALUES 
  ('Canarias','1984-02-01'),
  ('Cataluña','1996-04-03'),
  ('Canarias','2010-03-01'),
  ('Madrid','1999-01-01'),
  ('Madrid','2019-12-01');

And now the clarifications:

• Forget efficiency, this query necessarily requires two steps. Unless you define the date Fas INDEXand resolve to ORDER BY. In this case you have to take into account that, if two dates coincide, it will only show you the first record and not all those that coincide with that minimum date.

ALTER TABLE Hospital ADD INDEX(F);
SELECT H FROM Hospital WHERE PR='Madrid' ORDER BY F LIMIT 1;

• Never, ever, do this in a totals query:

SELECT H,MIN(F) FROM Hospital WHERE PR='Madrid';

It seems to work, but you just have to try with:

SELECT H,MAX(F) FROM Hospital WHERE PR='Madrid';

To verify that it continues to give the same result (it is usually the first one it finds), it does not matter if you look for the maximum or the minimum. The reason is because SQL doesn't work like that. A total and a projection are algebraic operations that we cannot mix. Either we calculate a total or we project, both things at the same time: no. You can search for a minimum with MIN()or locate which record a value corresponds to with WHERE. But keep in mind that they are two different operations.

• And lastly, the GROUP BY. Do not form groups unless you need them. What SQL does when it finds a GROUP BYis make separate heaps separated by the grouping fields, and compute totals for each group separately. If you are only interested in Madrid, he WHEREis perfect. We do not need the totals of other autonomies.

• And never, ever, group by a primary key. By definition, you will get as many groups as there are records, and the totals of each group will coincide with the value of each record. So, with a projection, you would arrive at the same result and at a much lower computational cost. Whenever we group by a field, it must have values ​​that are repeated in different records.

I hope this answer is useful to you. I remain at your disposal to clarify or expand any detail.

OPTION 1

Your grouping won't work because column H and column F have different values.

I would leave the query like this:

select H,min(F) from Hospital where PR="Madrid" GROUP BY H;

add at the end the use of

Limit 1

To indicate that the result obtained is limited to one record

OPTION 2

Performing a CTE within which we do:

• We select H, PRandF
• We make use of a WHEREwhere we indicate that Fthe date is equal to:
• Inside parentheses we write a sub query where we select the smallest date where PRit is equal toMadrid
• We do outside the CTE one SELECTof the columns that we indicate must return Menorwhat is the name that we gave to the * CTE *

Query

WITH Menor AS(
    SELECT H, PR, F
    FROM demo WHERE F = (SELECT MIN(F) FROM demo WHERE PR = "Madrid")  
)SELECT H, PR, F FROM Menor;

Result

H   PR  F
4   Madrid  1999-01-01

Or just take the query out of the CTE context and run it and it should work like this:

SELECT H, PR, F
FROM demo 
WHERE F = (SELECT MIN(F) FROM demo WHERE PR = "Madrid");

At the end you can get the result with a subquery:

mysql> select H from Hospital where F in (
       select Min(F) from Hospital where PR="Madrid");

Using MariaDB (I don't have MySQL at hand) I propose:

SELECT H 
  FROM (
    SELECT H 
         , MIN(F) 
      FROM Hospital 
     WHERE PR = 'Madrid' 
 ) SUB

Which returns me: