Help with select in MYSQL [closed]

You can give a select by sorting through the two columns that you want to use as a criterion. If I understand correctly, you want to give this select in the Cliente table. That way, it would look like this:

SELECT cidade, nome
FROM Cliente
ORDER BY anoIngresso ASC;

To make sure that the anoIngresso field is in the Cliente table, you can try:

SELECT cidade, nome
FROM Cliente
ORDER BY Cliente.anoIngresso ASC;

In both cases, it will take (and display) the fields cidade and nome , sorting them by the field anoIngresso , that is, from the oldest to the most recent.

And if you want to set a limit for up to a specific year, you can add WHERE as follows:

SELECT cidade, nome
FROM Cliente
ORDER BY anoIngresso ASC
WHERE anoIngresso < 2000;

That is, to show cidade and nome of customers who joined before the year 2000, by ordering them by anoIngresso itself.

[Edited]

The "problem" of SELECT DISTINCT with two fields is that it obviously takes the two fields into account. That is, if I have a Funcionarios table like this:

id   |   nome   |     cidade       | idade
1    |  João    |  Rio de Janeiro  |   30
2    |  Maria   |  Rio de Janeiro  |   27
3    |  João    |  São Paulo       |   28

And then run:

SELECT DISTINCT cidade, nome FROM Funcionarios ORDER BY idade ASC;

Will return:

    cidade     | nome
Rio de Janeiro | Maria
São Paulo      | João
Rio de Janeiro | João

Well, "Rio de Janeiro and Maria" and "Rio de Janeiro and João" are distinct ; as well as "São Paulo and João" and "Rio de Janeiro and João" are distinct .

So if you want to have only one client for each city, I think you will have to manipulate the data after the query, because it is only through MySQL that I can not get the desired result. / p>

I hope I have helped!

To solve the problem you must run the following command

select * from cliente where cidade = [ entre " " coloque o nome da cidade ] ORDER BY anoIngresso DESC LIMIT [ aqui você coloca o quanto que deseja que apareça ]

  

How do I list for each city the oldest customers

As the table structure has not been posted, I will suggest the following:

CREATE DATABASE 'teste' DEFAULT CHARSET 'utf8' DEFAULT COLLATE 'utf8_unicode_ci';
USE 'teste';
CREATE TABLE 'clientes' (
    'idCliente' int(11) NOT NULL AUTO_INCREMENT,
    'nome' varchar(255) NOT NULL DEFAULT '',
    'endereco' varchar(255) NULL DEFAULT NULL,
    'cidade' varchar(255) NULL DEFAULT NULL,
    'anoIngresso' smallint(6) NOT NULL DEFAULT 0,
    PRIMARY KEY('idCliente')
) ENGINE=InnoDB;

In order to test, I will insert some records. Note that the client to be returned contains an asterisk at the end of the name:

INSERT INTO 'clientes' ('nome', 'endereco', 'cidade', 'anoIngresso') VALUES
    ('João *', 'Rua A', 'Rio de Janeiro', 1992),
    ('José', 'Rua D', 'Rio de Janeiro', 1995),
    ('Joaquim', 'Rua H', 'Rio de Janeiro', 1997),
    ('Maria', 'Rua H', 'São Paulo', 2000),
    ('Janete', 'Rua 1', 'São Paulo', 1995),
    ('Malaquias *', 'Rua E', 'São Paulo', 1992),
    ('Zeca *', 'Rua X', 'Curitiba', 1997),
    ('Inácio', 'PF', 'Curitiba', 2018);

Finally, let's run the query -solution :

SELECT 'cl'.*
    FROM 'clientes' AS 'cl'
INNER JOIN (
    SELECT 'cidade', MIN('anoIngresso') AS 'menorAno'
    FROM 'clientes'
    GROUP BY 'cidade'
) AS 'cd'
    ON 'cl'.'cidade' = 'cd'.'cidade' and 'cl'.'anoIngresso' = 'cd'.'menorAno';

That returns:

+-----------+-------------+----------+----------------+-------------+
| idCliente | nome        | endereco | cidade         | anoIngresso |
+-----------+-------------+----------+----------------+-------------+
|         1 | João *      | Rua A    | Rio de Janeiro |        1992 |
|         6 | Malaquias * | Rua E    | São Paulo      |        1992 |
|         7 | Zeca *      | Rua X    | Curitiba       |        1997 |
+-----------+-------------+----------+----------------+-------------+
3 rows in set (0.00 sec)

  

Example:

     

city A - customer joao

     

City B - customer maria

     

city C - customer jose

Just change the query filter:

SELECT 'cl'.'cidade', 'cl'.'nome'
    FROM 'clientes' AS 'cl'
INNER JOIN (
    SELECT 'cidade', MIN('anoIngresso') AS 'menorAno'
    FROM 'clientes'
    GROUP BY 'cidade'
) AS 'cd'
    ON 'cl'.'cidade' = 'cd'.'cidade' and 'cl'.'anoIngresso' = 'cd'.'menorAno';

That returns:

+----------------+-------------+
| cidade         | nome        |
+----------------+-------------+
| Rio de Janeiro | João *      |
| São Paulo      | Malaquias * |
| Curitiba       | Zeca *      |
+----------------+-------------+
3 rows in set (0.00 sec)

A similar question has been solved here:

  

SOpt - How do I get the value of a column that matches the maximum of another column?